Sort all values (numeric) in matrix using DAX

henkplus — Thu, 22 Dec 2022 11:13:09 GMT

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

Your help is highly appreciated.

Re: Sort all values (numeric) in matrix using DAX

wdx223_Daniel — Fri, 23 Dec 2022 07:33:08 GMT

try these two steps

1, put both Locations and Year fields in the row

2, create a measure with RANKX to filter the visual

Re: Sort all values (numeric) in matrix using DAX

henkplus — Fri, 23 Dec 2022 11:47:54 GMT

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.

topic Re: Sort all values (numeric) in matrix using DAX in DAX Commands and Tips

Sort all values (numeric) in matrix using DAX

Re: Sort all values (numeric) in matrix using DAX

Re: Sort all values (numeric) in matrix using DAX