Browse 549 attributed reviews, viewable separately for our classroom and online training
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.

  1. Calculating the percentage share of the highest value in DAX
  2. The single measure solution
  3. 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:

Sales by region

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:

CALCULATE 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:

Sales by region

Each region's sales as a percentage of sales for the South East (the region with the most sales).

This blog has 0 threads Add post