Forum Discussion
Help with Ranking
Hi!
I'm looking for help with the ranking function:
I am looking for the DAX formula to rank Stores depending on the Value and within the same Cluster.
I have one table with Clusters and corresponding Stores in column; the Value is a calculated measure
For example:
- I have 5 Stores with their respective Cluster
- the same visual contains all Clusters (not filtered with any slicer)
- my current formula calculated the ranking on all stores :
Ranking = RANKX((ALLSELECTED(Stores));Value)
Here is the result:
| Cluster | Store | Value | Ranking |
| A | a | 5 | 5 |
| A | b | 10 | 4 |
| B | c | 50 | 1 |
| C | d | 30 | 2 |
| C | e | 20 | 3 |
Here is what I'm looking for:
| Cluster | Store | Value | Ranking |
| A | a | 5 | 2 |
| A | b | 10 | 1 |
| B | c | 50 | 1 |
| C | d | 30 | 1 |
| C | e | 20 | 2 |
How do I get the ranking by Cluster?
Thank you very much!
2 Replies
Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Maybe: https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Anonymous Try this, file is attached below my signature:
Measure = VAR OuterCluster = SELECTEDVALUE ( Ranking[Cluster] ) VAR OuterValue = [Total Value] VAR TempTable = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( Ranking, Ranking[Cluster], Ranking[Store] ), "Val", [Total Value] ), ALL () ) VAR FilterRows = FILTER ( TempTable, Ranking[Cluster] = OuterCluster && [Val] >= OuterValue ) VAR Result = COUNTROWS ( FilterRows ) RETURN Result
