Calculating % of Totals

No comments

A really common calculation that we require quite often is to calculate the % of a total amount. 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 % 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.

of-totals-5

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.

of-totals-6

 

What’s really cool now is when can start slicing the data by some of our other dimensions and get the % 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.

of-totals-7

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s