Table of Contents
To calculate the percentage of total by category in Power BI, you can use the “Quick Measures” feature. First, create a measure that calculates the total value for each category. Then, use the “Quick Measures” option to create a new measure that divides the total value by the grand total. This will give you the percentage of total for each category. You can then add this measure to a visual to display the percentage breakdown by category. Alternatively, you can use the “Group By” feature to group your data by category and create a calculated column that calculates the percentage of total for each category. This can also be added to a visual to display the percentage breakdown by category.
You can use the following syntax in DAX to create a new column that displays the percent of a column total by category:
Percent of Team Total =
'my_data'[Points]
/ CALCULATE (
SUM ( 'my_data'[Points] ),
ALLEXCEPT ( 'my_data', 'my_data'[Team] )
)
This particular example creates a new column named Percent of Team Total that displays the percent of the total of the Points column, grouped by the values in the Team column.
The following example shows how to use this syntax in practice.
Example: Calculate Percent of Total by Category in Power BI
Suppose we have the following table in Power BI named my_data that contains information about points scored by basketball players on various teams:
Suppose we would like to add a new column that shows how much each individual value in the Points column represents as a percentage of the total values in the Points column, grouped by Team.
To do so, click the Table tools tab along the top ribbon, then click the New column icon:
Then type in the following formula into the formula bar:
Percent of Team Total =
'my_data'[Points]
/ CALCULATE (
SUM ( 'my_data'[Points] ),
ALLEXCEPT ( 'my_data', 'my_data'[Team] )
)
This will create a new column named Percent of Team Total that shows how much each individual value in the Points column represents as a percentage of the total values in the Points column for each team:
If you’d like to display the values as percentages, then click the dropdown arrow next to Format and then click Percentage:
Each of the values in the Percent of Team Total column will now be displayed as percentages:
- The first Mavs player accounts for 25.88% of all points scored by the Mavs.
- The second Mavs player accounts for 16.47% of all points scored by the Mavs.
- The third Mavs player accounts for 22.35% of all points scored by the Mavs.
- The fourth Mavs player accounts for 35.29% of all points scored by the Mavs.
If we add up each of these percentages, we’ll find that the total is 100%:
Total Mavs Points: 25.88% + 16.47% + 22.35% + 35.29% = 100%.
The same is true for every other team in the table.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: