In this article, we will learn to implement basic bubble charts and waterfall charts using Power BI. This discusses some important concepts used to create the very common bar and column charts so as to make large business intelligence decisions. We will be discussing the following topics and their implementation in the Power BI desktop.
Pre-requisite: You can refer to Power BI interactive dashboards for easy implementation of the following charts.
Bubble Chart
A bubble chart is like a scatter chart that always displays two major value axes: one set of numerical data along the horizontal axis and another set of numerical values along the vertical axis. It is widely used to depict correlations between three or more numerical variables. The color of the bubble or its movement in animation can sometimes symbolize additional dimensions. By adding a third numeric column that determines the size of the data.
This chart shows the intersections of two numeric values with a bubble, The third dimension is the size of the bubble which is useful for the final evaluation.
When to use Bubble Chart
- Whenever we want to create a relationship between two numeric values with their size.
- The third metric (size) helps in determining the value differences of various intersections.
- It also helps in analyzing financial data.
- To display data with quadrants.
DataSet Used:
The dataset used is “SaleData“. Upload the dataset in Power BI and refer to the dataset to follow along with the below-given sections of the article.
Data: We will be working with “SaleData” data with data fields as shown in the above image. The major variables used to show the charts are as follows.
- Units: This is the data field used in the x-axis for showing the number of units taken.
- Unit_price: This is the data field used in the y-axis for showing the price of one unit of any item.
- Brand: This is the parameter used for showing colorful legends representing various item brands.
- Sale_amt: This is the data field used for the size of the bubble. The average of Sale_amt is taken for this metric.
Load Data in Power BI Desktop
- Open Power BI Desktop
- Click the “Get Data” option and select “Excel” for data source selection and extraction.
Select the relevant desired file from the folder for data load. In this case, the file is “SaleData.xls”. click the “Load” button once the preview is shown for the Excel data file.
The dashboard is seen once the file is loaded with the “Visualizations” Pane and “Data fields” Pane which stores the different variables of the data file. The red icon is used for bubble or scatter charts. Drag the icon to the main canvas for showing or building the visuals.
As mentioned earlier, the different axis is set by dragging the data fields.
Bubble Chart in Power BI: The following shows the Average of Sale_amt for different item brands by units and their prices. The size of a bubble is directly proportional to the average Sale_amt.
On hovering over any bubble, it displays all the needed metrics for analysis.
Output: The above is shown in the video GIF format. Keep hovering on the different size bubbles to get maximum data.
All of the above steps are applied to “Region” as well. The “Visualizations” pane is set with the same x-axis, y-axis, and Size. This time the “Legend” is set to “Region” so as to build a visual based on different regions namely “Central”, “East” and “West”.
Video Output:
Example 2: The following also demonstrates another bubble chart for Total Articles published (for different technologies shown with different colors ) for all the months in a year. The x-axis shows the months, the y-axis shows the total articles published in a month and the size of the bubble shows the total articles published based on both.
Dataset used: “techDataSet.xlsx“
Set Visualization Pane:
The result shows colored bubbles (legend) and the number of articles (line y-axis) published in any month (x-axis). The y-axis shows the “Total_Published” as it shows the count for any one technology namely “AI”, “Data Science”, HTML”, “ML”, “PHP”, or “Python” published in any particular month.
The following shows the total number of articles published in the month of “March” is 14 which is based on “AI” technology.
The following shows the total number of articles published in the month of November is 98 which were based on the “PHP” technology.
When the user hovers on different sized bubbles, the user can see data with better understanding.
Waterfall Chart
A waterfall chart shows a running value as quantities are added or subtracted. It’s helpful to visualize how an underlying value is influenced by a series of positive and negative changes. The columns are usually encoded using color so that you can quickly identify increments and decrements.
In a waterfall chart, a particular bar is positioned at the end of the previous bar and the baseline is based on whether there is an increase or decrease in the previous value.
These are also called Bridge charts showing the running total of a set of added and subtracted values. The waterfall charts have color-coded columns to represent the increase or decrease of values in an easy manner. It shows how an initial value is affected by changes in the chart denoted by a column or vertical bar. The intermediate values can fluctuate between initial and final values.
When to use Waterfall Charts
- To show changes in data across time or some different categories.
- To show major incremental or decremental changes that contribute to a total value.
- To analyze data based on profit or loss scenarios like the annual profit of a company.
- To represent the beginning and end of some count scenarios.
- When we want to analyze actual values and targeted ones over time.
Common Examples:
- Monthly spending or Savings of a household.
- Annual Profit or Total Revenue generated in a company.
Note: The same steps are to be taken as shown earlier in the “Bubble chart” section for opening the Power BI desktop and navigating the data source before loading it.
When the “Visualizations” pane is opened, use the following icon for implementing the waterfall chart into the Power BI canvas.
Initial Canvas:
DataSet used: We will be working with “techPublishedDataSet” data with data fields as shown in the image. The major variables used to show the charts are as follows.
- Month: This is the data field used in the x-axis for showing the months of a year.
- Article_variance: This is the parameter used for showing the y-axis. Article_variance is the new column created that shows the value of (Total_Published – Expected) articles.
Dataview in Power BI desktop: ‘Sheet1’ is the sheet of the “techPublishedDataSet.xlsx” file.
Set the Visualization Pane: “Sum of Article_variance” is taken in the y-axis as we are taking the summation of total published articles including all different technologies in any given month.
Output:
On hovering on any column: