Sort all values (numeric) in matrix using DAX

[henkplus]

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

[henkplus]

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]

try these two steps

1, put both Locations and Year fields in the row

2, create a measure with RANKX to filter the visual