Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
henkplus
Regular Visitor

Sort all values (numeric) in matrix using DAX

- Question: How to sort all values in matrix using DAX? Goal: pick N highest / lowest values with relevant dimensions.

- Description: Matrix Trenddev has 2 dimensions: year (number) and location (number). The values (%) in the matrix represent the deviation between: (1) the actual value (measure, based on the actual value for that specific location and year), and (2) the calculated value  based on the linear trend (measure using the actual data for the analysis period).

- Analysis period (start and end year): variable thru a slicer, max 18 years.

- Number of locations: max 3.000.

- Goal: get the N values (N = e.g. 5 or 10) with the highest (positive) / lowest (negative) values. In other words: where has the largest deviation occurred and when? For the outcome I need the value + location and year in order to be able to do further research into the how and why.

- Example: the attached Excel example is simplified for illustration and reference.Matrix Trenddev.png

Your help is highly appreciated.

2 REPLIES 2
henkplus
Regular Visitor

Thnx for your reply.

Unfortunately I'm afraid I don't follow you in your first step.

Can you show what you mean with your first step?

If you have help for creating that measure that would be nice too.

Thanks.

wdx223_Daniel
Super User
Super User

try these two steps

1, put both Locations and Year fields in the row

2, create a measure with RANKX to filter the visual

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors