Do you want to show the percentage of the total in a Power BI Pie chart? In this Power BI tutorial, we will see how to display the percentage of the total on a pie chart in Power BI.
Also, we will cover the below examples:
- Power BI pie chart percentage of total
- Show the percentage of the total with distinct counts in the Power BI Pie chart.
- How to show a percentage from two-column values in a pie chart
- Show the total percentage in the Power BI pie chart when the filter is selected
Power BI pie chart percentage of total
Here we will see how to show the percentage of the total in the Power BI pie chart.
For example, we will use the below sample table which contains the Product and Sales column.
We will create a Pie chart and display sales based on the Product. By default, the Detail label shows the data value, so we will change it to the total percentage. For this, follow the below steps:
- Open Power BI Desktop, then Load the data using the get data option.
- To create a Pie chart, select the Pie chart from the visualization pane.
- Then select the Product field as Legend, and the Sales field as Values.
- To show the percentage of the total in the Pie chart, click on the format pane, then expand the Detail labels.
- Then expand the Options, and select the percentage of total options in the Label content.
- Now you can see the percentage of the total in the Power BI Pie chart.
Show the percentage of total with distinct counts in a Power BI Pie chart
Here, I will explain how to show the percentage of the total with distinct counts in the Power BI Pie chart.
For example, the table below contains the Table, Id, and Date columns.
We will create a pie chart that will show the Distinct Count of ID based on Type and the percentage of the total distinct counts.
- Open Power BI Desktop, then Load the data using the get data option.
- Now we will calculate the Distinct Count of ID, for this click on the +New measure.
- Then write the below formula to distinct count ID based on type.
Distinct Count of Id = DISTINCTCOUNT(Table1[Id])
- To create the Pie chart in Power BI, select the Pie chart from the visualization pane.
- Then drag and drop the Type to ‘Legend‘ and Distinct count of Id to Value.
- To show the distinct count and percentage of the total in the Pie chart, click on the format pane, then expand the Detail labels.
- Then expand the Options, and select the Data value and percent of total options in the Label content.
Now you can see the Distinct count with the percentage of the total based on type in the Pie chart.
Let’s see how to show the percentage of the total with distinct counts in a Power BI pie chart.
Show a percentage from two-column values in a Power BI Pie Chart
Here, we will see how to show a percentage from two column values in a Pie chart.
For example, we have the sample response rate table, which has two columns i.e. Employee and Response.
To show the percentage of response and non-response based on Total employees =10 and Total response =6, you can see in the above data.
So, in the Pie chart, I want to show the responded percentage as 60%, whereas the other/ non-responded percentage is 40%. It will look like below
To achieve this, I have followed the below steps:
- Open Power BI Desktop, then Load the data using the get data option.
- To calculate the Responded percentage, for this, click on the New measure in Power BI. Then, in the Formula box, write the below formula
responses = SUM('Table'[Response]) / SUM('Table'[Employee])
- To calculate the non-responded percentage, for this, click on the New measure. Then, in the Formula box, write the below formula
other = (SUM('Table'[Employee]) - SUM('Table'[Response])) / SUM('Table'[Employee])
- Select the Pie chart from the visualization pane to show a percentage of the total from 2 column values in the Pie chart.
- Then drag these two measures i.e. ‘other‘ and ‘response‘ to the Values field.
- To show only the percentage, for this, click on the format pane icon.
- Expand the Detail label icon, then select the Label content as Percent of total.
- Now you can see the percentage detail label in the Pie chart.
This is an example of how to show a percentage from two-column values in a pie chart.
Show the total percentage in the Power BI pie chart when a filter is selected
Here, we will see how to show the total percentage in the Power BI pie chart when the filter is selected.
For example, we have the below sample table, which contains the Product and Sales columns.
Here we will create a pie chart, that will show the total percentage for each product based on other products in the Pie chart.
Let’s user select Product A, then it will show the 40% and the other products 60%. Similarly, if the user selects Product B, then it will show 32% and the other products 68%. For Product C, it will show 28%, and for other products it is 72%.
Following the steps to show the total percentage in the Power BI pie chart when the filter is selected.
- Open Power BI Desktop, then Load the data using the get data option.
- Then click the Transform data option in the ribbon to open the Power Query editor.
- We will create a dim table that contains the Product column, for this right click on the Product table -> then click on the Duplicate table.
- Next, rename the Product(2) table as Product.
- Then delete the sales column from the table by clicking on the Remove column.
- To add ‘Other’ value to the product column, click on Advance editor, and write the below code to add Other. Then click on Done.
#"Add row"= Table.InsertRows(#"Renamed Columns", 3,{[Product="Other"]})
in
#"Add row"
Now, you can see Other is added to the Product column in the table. Now our data is ready, you can click on Apply and Close.
- In Power BI Desktop, to create a new measure, click on the New measure.
- Then write the below measure in the formula box, which will filter the sales based on the product category.
Measure = var _filter = CALCULATE(ISFILTERED('Product'[Product]), ALLSELECTED())
return if(_filter, If(Max('Dim'[Product]) ="Other", CALCULATE(sum('Product'[Sales]), all()) -CALCULATE(sum('Product'[Sales]),ALLSELECTED() ) ,sum('Product'[Sales])), sum('Product'[Sales]))
- To create the Pie chart, select the Pie chart from the visualization pane.
- Then drag and drop the Product table to ‘Legend‘ and Measure to ‘value‘ field.
- To filter the visual, select the slicer from the visualization pane, then add the Product column to the Field.
- Here you can select the A in the slicer, and you can see the total sales percentage in the Power BI pie chart for Product A.
This is how to show the percentage in the Power BI pie chart when the filter is selected.
To check all the solutions, you can download the zip file. Unzip it and open the .pbix file in Power BI Desktop.
Conclusion
In this Power BI Desktop tutorial, we saw how to show the percentage of the total in the Power BI Pie chart.
Also, we cover different examples available in Power BI.
- Power BI pie chart percentage of total
- Show the percentage of the total with distinct counts in the Power BI Pie chart.
- How to show a percentage from two-column values in a pie chart
- Show the total percentage in the Power BI pie chart when the filter is selected
You may also like:
- Power BI Pie Chart Conditional Formatting
- Power BI Pie Chart Drill Down
- How to Show Only Selected Values in Power Bi Pie Chart?
After working for more than 15 years in Microsoft technologies like SharePoint, Office 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (9 times). I have also worked in companies like HP, TCS, KPIT, etc.