Simple RANKX instructions - how to get rank value on a matrix with slicers?

[Anonymous]

Hello,

I want to achieve something seemingly simple, alas I'm failing miserably!

I have a table that contains unpivoted KPI metrics (e.g. awareness, quality, intention, etc)

I have created a report page with the following slicers:

- Date range

- Country

- Sector

- Metric

I have created a matrix that returns the average value based on the above filter selections, for example:

Brand                      Average of Score

Coke                                59.9

Pepsi                               58.6

Fanta                               46.1

Sprite                              43.0

What I want to achieve, is a table that contains the ranking value instead of the average score, so:

Brand                           Rank

Coke                                1

Pepsi                               2

Fanta                               3

Sprite                              4

Although this seems extremely easy, I have spent a good hour looking for a solution, but haven't managed to make it work. What confuses me is whether to use ALL, ALLEXCEPT, etc. and how as the Rank measure has to abide by all slicer selections for it to work.

Any help is much appreciated!

George

[MattAllington · Accepted solution]

It is not possible to give you an accurate answer without knowing all the tables, columns and relationships.

Assuming there are none... and you have a measure called Average of Score

myRank = rankx(all(tableName[brand]),[Average of Score])

[Anonymous]

Thank you very much Matt! Beautiful in its simplicity.

I had not thought of creating a Measure for the score (was using column average in my tab), which made the difference.

Thanks again,

George.