In this writing, I want to share how I use pie chart in my Power BI report, even if some other Power BI authors do not prefer to use the pie chart. In my opinion, Pie chart is a great visualization if authors create it properly to show relevant information to readers.
I use a below sample in order to describe how to properly use pie chart.
Datamodel
Sales table
In some cases, authors create a pie chart like below, and then say that the pie chart does not demonstrate data properly to show insightful information.
I also agree that the above figure does not explain any insightful information. Because, firstly, there are quite a large number of categories shown in one pie chart. And secondly, it is hard to identify which category has the largest quantity or the second largest quantity.
However, think about what insights readers can get if authors create parameter to select TOPN number, and then let pie chart show TOPN categories + Others?
For instance, when readers select 4, then Top 4 categories + Others (total five areas) show on the pie chart and it makes easier for readers to get insightful information from the pie chart.
There are many ways to achieve this, and in this writing, I will demonstrate how I achieve this.
In order to create this, the first step is to create a disconnected Category table with "Others" like below. This disconnected table can be created in Power Query editor. The index number of "Others" can be defined as, Max index number from the Category table plus one.
In the new pie chart, insert Category column from the new table (Category with Others table) into the legend field, and put measure = TopN quantity: into the Values field. The measure can be written like below.
TopN quantity: =
VAR _topnselect =
SELECTEDVALUE ( 'TopN'[TopN] )
VAR _topnqty = // It shows topn quantity per each category
CALCULATE (
SUM ( Sales[Quantity] ),
TOPN (
_topnselect,
ALL ( Category ),
CALCULATE ( SUM ( Sales[Quantity] ) ), DESC
),
TREATAS ( DISTINCT ( 'Category with Others'[Category] ), Category[Category] )
)
VAR _allqty =
SUM ( Sales[Quantity] )
VAR _othersqty =
_allqty
- CALCULATE (
SUM ( Sales[Quantity] ),
TOPN (
_topnselect,
ALL ( Category ),
CALCULATE ( SUM ( Sales[Quantity] ) ), DESC
)
) // It shows topn quantity by all categories in topn
RETURN
IF (
HASONEVALUE ( 'Category with Others'[Category] ),
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Category with Others'[Category] ) = "Others", _othersqty,
_topnqty
),
SUM ( Sales[Quantity] )
)
The result of the visualization looks like below. It shows more insightful information than having all 15 categories in the pie chart.
If readers still want to see all 15 categories, then slicer can be selected to show 15 instead of 4.
To summarize, Pie chart provides insightful information when Power BI authors use it properly. I hope this helps having more fun in utilizing Pie Chart in Power BI.
Comments