Table of Contents
Calculating the percent of total in Power BI is a simple process that allows you to analyze data in a more meaningful way. To calculate the percent of total, you will need to use the DAX formula in the Power BI formula bar. This formula is used to divide the desired value by the total value and multiply it by 100 to get the percentage. The resulting percentage will show the proportion of the selected value in relation to the total value. This calculation can be used to compare different categories or values within a dataset and gain a better understanding of their significance. By utilizing this feature, you can effectively visualize and interpret data in your Power BI reports.
Calculate Percent of Total in Power BI (With Example)
You can use the following syntax in DAX to create a new column that displays the percent of a column total:
Percent of Total = 'my_data'[Points] / SUM('my_data'[Points])
This particular example creates a new column named Percent of Total that displays the percent of the total of the Points column that a given row represents.
The following example shows how to use this syntax in practice.
Example: How to Calculate Percent of Total 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.
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 Total = 'my_data'[Points] / SUM('my_data'[Points])
This will create a new column named Percent of Total that shows how much each individual value in the Points column represents as a percentage of the total values in the Points column:
If we add up all of the values in the Points column, we’ll find that the total is 256.
Thus, we can see from the Percent of Total column:
- The first player accounts for 22/256 = .0859 or 8.59% of total points.
- The second player accounts for 14/256 = .0546 or 5.46% of total points.
- The third player accounts for 19/256 = .0742 or 7.42% of total points.
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 Total column will now be displayed as percentages:
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: