This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Calculating the percentage share of the highest value in DAX The single measure solution (this blog) A solution using a calculated column

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.

Calculating the percentage share of the highest value in DAX
The single measure solution (this blog)
A solution using a calculated column

Posted by Andy Brown on 22 January 2021

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!

Parts of this blog
Calculating the percentage share of the highest value in DAX The single measure solution (this blog) A solution using a calculated column

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

The single measure solution

Head office

London

Manchester