Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])
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])
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |