Power BI percent of the total is a really common calculation that we require quite often. This is great to quickly show where the larger portion of results sits versus the entire population.
Completing this in Power BI does not require much-written code, but it does require some understanding. It will generally be one of the first introductions to the CALCULATE statement.
Let’s start by running through an example.
First, let’s create a total sales calculation. This is easy.
Now we can place it next to one of our dimensions, say customers, and we will return the total sales for each customer.
Now, how can we calculate the percent of total? Well, we have to get the sales for ALL customers and somehow place it onto every single row. We need to divide every single total sales result beside each customer by the total for ALL customers.
The only way to do this is to use the CALCULATE function to change the filter context for the total sales measure.
Let’s see what happens when we bring the ‘Sales for ALL Customers’ measure into the table next to customers
So you see that we have achieved what we need. We can now divide the ‘Total Sales’ measure by the ‘Sales for ALL Customers’ measure and get the % of total as the result. Let’s do that.
Great, now we have our results aligned to each customer name. In the results here we can immediately see that the sales are rather distributed across all our customers with the highest customers only having 2.64% associated to them.
We don’t even need these intermediary numbers anymore. We can just show the percentage.
What’s really cool now is we can start slicing the data by some of our other dimensions and get the percent of totals for the new context (our new filters).
Let’s check this out by bringing in products and countries, and see how our results change.
***** Related Links *****
Calculating Dynamic Percentage Of Total Change Using Power BI Time Intelligence
Calculating Percent Profit Margins Using DAX In Power BI
Analyze Profit Margin Changes Overtime – Analytics With Power BI And DAX
As you can see, the results now dynamically adjust for any selection we make. We can select a country, then a product, and quickly identify who our best customers are for that selection.
I would add here that we achieve these results only because we have written our DAX function using the CALCULATE statement against the Customers context. If we placed this measure against a different dimension like from the regions table of products table, we would receive weird results.
That is why you need to understand the filter context well. It will be something that we will touch on many times over in time.
For more information about Power BI percent of total, check out the links below.
Good luck on this!
Sam
