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

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.

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

Posted by Andy Brown on 22 January 2021

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:

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(

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

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

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Visual C# training

Excel training courses

Power Automate training

Power BI / DAX training

Python training courses

SQL training courses

VBA (macros) training

Site map

Terms & Conditions

BLOGS BY TOPIC

BLOGS BY AUTHOR

A solution using a calculated column

Head office

London

Manchester