Power BI Tip: Create a bar chart with a total bar.
When you’re making bar charts or column charts in Power BI (a tool for showing data visually), sometimes you want to add a special bar. This bar shows the total of all the other bars in the chart. This is handy because you can then easily compare how much stuff, like sales or money earned, you’ve got each month, without needing a whole new chart.
Also, it can be helpful to put this special total bar in a certain kind of chart where bars are put next to each other for each month (this is called a “clustered” bar chart). This makes it easier to see how each part contributes to the whole. It’s better than putting everything on top of each other (in a “stacked” bar chart), which makes it tricky to figure out how much each separate thing is worth.
So, in simpler terms, this message talks about adding a total bar to your charts in Power BI. It helps you compare monthly totals without making extra charts and using the right kind of chart makes it even easier to understand how different parts make up the big picture.
Steps Summary
- Create a new table with a distinct product list and a “total” row.
- Define a relationship between the new table and the dataset.
- Create a new measure that will give the sum of all products against the total.
- Create a chart using the column from the new table and the new measure.
You can follow this tutorial and create a bar chart (clustered column chart) with a total bar.
Step 0: Create a dataset.
- Click ‘Enter data’
- Type the data as shown below. This dataset records the revenue from each product (A, B, C) each month.
- You can change the table name if you want.
Step 1: Create a new table with a distinct product list and a “total” row.
- Change to the ‘Data View’ and Click ‘New Table’
- Type the below code to create a new table named ‘All_Product_Total’.
All_Product_Total = UNION(DISTINCT(Table[Product]),{"Total"})
This table will union (combine) the distinct list of products with a “Total” row. (You can change from ‘Table’ to your table name and [Product] to your column name)
Step 2: Define a relationship between the new table and the dataset.
- Change to the ‘Model view’ and drag the product column from the new table to the dataset to create a relationship. Make sure that it is a 1-* relationship.
Step 3: Create a new measure that will give the sum of all products against the total.
- Back to the ‘Report view’ and click on ‘New measure’
- Type the below code to create a new measure ‘Total_Revenue’
Total_Revenue =
SWITCH(TRUE(),
SELECTEDVALUE(All_Product_Total[Product]) = "Total"
, CALCULATE(SUM('Table'[Revenue]), ALL(All_Product_Total[Product]))
, SUM('Table'[Revenue]))
You will see a new measure shown on the Data pane.
Step 4: Create a chart using the column from the new table and the new measure.
- Click on the ‘Clustered column chart’ button and select the ‘Product’ column from the new table and a new measurement ‘Total_Revenue’
Done! The total bar shows up.
- Sort the X-axis to make a total bar on the rightmost.
- You can also change the X-axis to ‘Month’ and Legend to ‘Product’. You can see the revenue of each product and the total revenue each month in the same graph.
Conclusion
Enhance data visualization in Power BI by adding a total bar to bar/column charts for quick comparisons. The total bar’s value summarizes cumulative metrics like sales and revenue. The steps involve creating a new table, defining relationships, crafting a measure for total calculations, and designing the chart.
Hope this article is useful for you. Please clap and follow me for more data science articles.
Articles you might interested in:
- A terrific way to show data with report tooltips in Power BI
https://medium.com/mlearning-ai/a-terrific-way-to-show-data-with-report-tooltips-in-power-bi-560e31c639cd - Clustering in Power BI
https://medium.com/mlearning-ai/clustering-in-power-bi-e1c7fd5700e - Python in Power BI
https://medium.com/mlearning-ai/python-in-power-bi-66a80590ecc0
