560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
How to use DAX to show for each row of a table its share of the maximum value
Part three of a three-part series of blogs
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.
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 not on the totals row, do the calculation
-- divide total sales for the region ...
-- by the highest value of total sales
-- for all regions
-- otherwise if on the totals row, show 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).
|Parts of this blog|
25 Aytoun Street