SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Calculate Percentage Share in Power BI (DAX)

What’s my share? Calculating percent share of the total is a common need. Let’s see how to calculate this in Power BI using DAX.

I’ll start with a table containing population by country as shown below. I’m using Power BI Desktop for this demonstration. You can use the same formula anywhere you’re allowed to write DAX.

pct share dax source table

First create a new measure called TotalPop using the following formula.

TotalPop = SUM( PopulationPctShare[Population] )

This is the same as the Population measure that already exists in my table. I still created a new measure because I recently learned that creating my own measure has a lot of advantages. So I’m going to force myself to use this practice.

Then create another measure with this formula.

GrandPop = CALCULATE( [TotalPop], ALL(PopulationPctShare) )

Both these measures when added to a table should appear as shown below.

percent share dax grand total

Notice that GrandPop is the same for all rows. This is the grand total of population of all countries. The ALL() expression removes the existence of filters, so when CALCULATE() evaluates TotalPop which in turn is SUM([Population]), it’s evaluated on the whole table without any filters.
And the grand total is repeated across all rows.

Lastly, create another measure using the following formula to calculate the percent share of the total.

CountryPctShare = [TotalPop]/[GrandPop]

Make sure to change the Format of CountryPctShare to Percentage. When I add TotalPop and CountryPctShare to a table, it should like the image below.

percent share in dax

Filtering

Let’s try something. If I randomly select 5 countries in the above table, I expect the calculations to change and appear in the context of my selection and that the sum of those 5 countries should be 100%.

percent share dax power bi all selected example

But I don’t see that. It’s because ALL() is used in the filter argument of CALCULATE() and it’s still being evaluated in the context of the full table because ALL() removes the existence of any filters. To overcome this, use ALLSELECTED() instead of ALL().

Sam Vanga

Sam Vanga helps business people solve their data problems and enables them make data-driven-decisions. His specialties include data integration, data warehouse and database development, and BI design. Sam blogs at http://SamuelVanga.com.

Comments

Leave a comment on the original post [samuelvanga.com, opens in a new window]

Loading comments...