cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

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

1 ACCEPTED SOLUTION

Accepted Solutions

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])



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

2 REPLIES 2

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])



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors