Browse 554 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 two 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 (this blog)
  3. A solution using a calculated column

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.

The single measure solution

At the heart of this problem lies the concept of a table of the sales for each region, from which we want to extract the highest value.  To make it easier to understand what's going on, I first created a DAX query to show this data in the separate DAX Studio add-in:

EVALUATE

-- show for each region its total sales

SUMMARIZE (

Region,

[RegionName],

"Total sales",

SUM(Purchase[Quantity])

)

Here's what this shows:

Sales by region

Exactly what I want - a table showing sales by region.

To pick out the highest sales value I can (back in Power BI) create a measure incorporating this:

Interim 1 =

CALCULATE(

-- get the highest value ...

MAXX(

-- ... from the table of sales by region ...

SUMMARIZE (

Region,

[RegionName],

"Total sales",

SUM(Purchase[Quantity])

),

-- ... of the sales

[Total sales]

),

-- do this for all regions

ALL(Region)

)

Here's what this would show for a table of regions:

Showing the maximum sales

For each region the measure shows the highest sale value for all regions.

 

We're nearly there - all that you now need to do is to divide the region's sales by this maximum figure (I'm going to use a variable to hold this to make it clearer what's going on):

Percent of highest regional sales =

-- first get the maximum of the total sales for all regions

VAR HighestRegionalSales =

CALCULATE(

-- get the highest value ...

MAXX(

-- ... from the table of sales by region ...

SUMMARIZE (

Region,

[RegionName],

"Total sales",

SUM(Purchase[Quantity])

),

-- ... of the sales

[Total sales]

),

-- do this for all regions

ALL(Region)

)

RETURN

IF(

-- if we're filtering by the region, divide

-- this region's total sales by sales for the

-- highest region; otherwise, show a blank (this

-- will make sure there's nothing in the total row)

ISFILTERED(Region[RegionName]),

DIVIDE(

SUM(Purchase[Quantity]),

HighestRegionalSales

),

BLANK()

)

This would give the required answer: 

Sales by region

The South East figure shows (as you might expect) as 100%, since this region was the one with the highest total sales.

A typical DAX formula - no one ever said that DAX was easy!

This blog has 0 threads Add post