WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 520 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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:

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:

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:

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!