We have a peer review process at my work, which has always been a great opportunity to learn. I remember in one review; I had critical feedback as to why I had used a pie chart on a report – pie charts are bad.
This review led me to read up on pie charts and lots more information around data visualisation. I found the storytelling with data books enlightening.
There are plenty articles out there on why you should not use pie charts:
So, all pumped I went back to my stakeholders with examples as to why the pie chart is bad and why we should use a bar chart instead on the dashboard.
Random example if you compare the two charts. In which one can you easily spot that bats and giraffes are the equal number. How easy is it to order all the categories?
However, with my stakeholders, the response was crickets and then we want the pie chart back 😉
So, my next question was why? For this, I had to dig a little deeper.
What we are often creating with Power BI are reports and dashboards that are interactive for other people to explore the data and then they explain it.
Kind of like Kevin Costner and the fields of dreams as a random analogy. We are building a baseball field, adding bases and handing out bats (often hoping someone actually walks onto the field one day), but the users requesting the reports / dashboards are the ones that need to hit the home runs with the insights they find when they start playing on it.
What I learnt is not that they really preferred the pie chart over the bar chart, but the real reason they wanted it was that it shows numbers and percentages by default (%).
Yep, we have a pretty graph but it’s all in the numbers 😉.
This goes back to the point of building exploratory dashboards and reports. Someone else needs to look at them and then explain insights from it. For this they still need the figures. The visual gives them the insight but when they communicate that this bar is very big, they need the actual numbers and the percentages.
Numbers are useful but if you say there were 30 cats, that detail doesn’t provide context. However, when say 30% of the results were cats first, that sets the scene for explaining a graph. Then you can go into the detail on the actual number of cats we are dealing with and why it’s the major category etc.
Solution – How to add Percentages (%) to your bar graph
As a visual for a subject matter that has a lot of categories I still think the bar chart is a better option than a pie chart. But by default, Power BI doesn’t give you numbers and percentages for it.
So, what to do?
I’ve tried several options on different stakeholders but below is the one I like the best as it allows me to use a bar graph (or column chart), but it still presents both the numbers and percentages how my stakeholders liked to see it in their favourite pie charts.
Pre-Requisite: You need tabular editor as going to create a calculation group. Refer to this article and the link to tabular editor.
- Create two measures.
- First one is just counting records or summing values to get an absolute value.
- The second is the percentage (%) calculation.
Key thing to note with the percentage calculation is that I’m using ALLSELECTED instead of ALL.
Frogs for example make up 16.1%. My initial thought was even if you filter down to only show frog as an animal, wouldn’t you still want to know the actual percentage frog’s makeup of the total?
However, in my case it caused confusion as users expected if we only have frogs selected then the graph should now display 100%. This actually makes sense 😉.
Likewise, if you have multiple levels to drill down on in the graph, showing percentages that add up to 100 % at each level became even more important to avoid confusion.
2. Need to create a calculation group in tabular editor. This is to format the measures.
To create the calculation group right-click on Tables and new Calculation Group.
Then on the calculation items, right-click and select “New Calculation Item”. Note you can create multiple calculation items to format different measures and bar graphs under the one calculation group.
Click on the calculation item and then in the Expression Editor type SELECTEDMEASURE().
This is used to apply the formatting to the measure that will be applied to the bar graph.
Change the property from Expression to Format String Expression so includes absolute and % values for each.
Below is the syntax the items in [] are the names of the measures I created. This formats the measure in the graph concatenating the two measures to show both absolute and percentage when applied.
Save in tabular editor and then save in Power BI desktop. Calculation group is your model now.
Apply the Calculation Item as a visual filter on your bar graph.
Now we have absolute and percentage values shown on a bar graph and we can replace the pie chart with a more effective visual for lots of categories (I think).
I learnt this technique from here (How to Power BI). Watch for a more impressive video presentation (plus has a better beard).
Above shows how I found this useful, as well as needing to think about how I was calculating percentages for the visual.