Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
545 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
|
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.