Power BI Percent Of Total – Using CALCULATE Statement

One comment

Power BI percent of total is a really common calculation that we require quite often. This is great to quickly show where the larger portion of results sit 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.

of-totals-1

Now we can place it next to one of our dimensions, say customers, and we will return the total sales for each customer.

of-totals-2

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.

of-totals-3

Let’s see what happens when we bring the ‘Sales for ALL Customers’ measure into the table next to customers

of-totals-4

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.

power bi percent of total

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.

power bi percent of total

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.

power bi percent of total

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

Membership Banne
Center of Excellence

***** 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

***** Related Course Modules *****
Mastering DAX Calculations
Advanced DAX Combinations
Solving Analytical Scenarios w/Power BI & DAX

***** Related Support Forum Posts *****
Looking To Calculate The Percent Of Total In Power BI Using DAX
Percentage Contribution To Total Sales
% Of Total Not Showing Correctly
For more percent of total support queries to review see here…

1 comments on “Power BI Percent Of Total – Using CALCULATE Statement”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.