BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
How can you show each aggregate value in a group as a percentage of the highest aggregate value? This blog shows two ways to use DAX measures to solve this deceptively difficult problem.
- Calculating the percentage share of the highest value in DAX
- The single measure solution
- A solution using a calculated column (this blog)
Posted by Andy Brown on 22 January 2021
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
A solution using a calculated column
I began the previous part of this blog by saying that at the heart of this problem is the idea of a table showing total sales for each region, so we could just add this column to the table of regions:
Sadly, this doesn't seem to work - the calculated column just shows for each region the total sales for all regions.
We can solve this by adding context transition:
The calculated column creates a row context for each region, but the CALCULATE function then creates a filter content within this to consider data just for each region.
If the above sentence makes no sense, don't worry too much about it - just accept that this works!
The final measure is now relatively simple:
Percent using calculated column =
IF(
-- if not on the totals row, do the calculation
ISFILTERED(Region[RegionName]),
DIVIDE(
-- divide total sales for the region ...
SUM(Purchase[Quantity]),
-- by the highest value of total sales
-- for all regions
CALCULATE(
MAX(Region[Total sales]),
ALL(Region)
)
),
-- otherwise if on the totals row, show blank
BLANK()
)
And again, this would give the required answer:
Each region's sales as a percentage of sales for the South East (the region with the most sales).
- Calculating the percentage share of the highest value in DAX
- The single measure solution
- A solution using a calculated column (this blog)