Forum Discussion
Dynamic ranking use RANKX() show duplicates
I have a dataset "table" as follows:
| Country | datetime | event | channel | price |
| UK | 2025-01-01 | 5 | BBC | 200 |
| US | 2025-01-03 | 3 | ABC | 198 |
Purpose is to give all the channel a ranking, based on their #event_per_hour and € Cost per event . To do that I bulild the measures below and by rank the #event_per_hour desc and €cost_per_event asc, they receive two rankings, then I sum up the two rankings and got a score, at the end I sort the channel by the score asc to have the final ranking "§TOTAL RANKING".
Until sum the two rankings to get the score it works fine. But when I use the final measure § TOTAL RANKING, the weird thing happens, the total ranking doesn't sstart from 1 and has duplicates, see these examples:
| Score | current Total ranking | expected Total ranking |
| 2 | Excluded | Excluded |
| 7 | 5 | 1 |
| 7 | Excluded | Excluded |
| 7 | 5 | 1 |
| 7 | 5 | 1 |
| 11 | 6 | 2 |
| 14 | 6 | 3 |
Can someone tell me what causes this problem and how to fix it? The visual is being filtered by the column "Country", each time one single selection of the slice "Country".
Measures:
- # Channel_count = CALCULATE(COUNT(table[channel]))
- # Sum_event = SUM(table[event)]
- # event_per_hour= DIVIDE([# Sum_event], [# Channel_count],0)#
- € Total cost = CALCULATE(SUM(table[price]))
- € Cost per event = (DIVIDE([€ Total cost],[# Sum event],0))
- Test_ranking_event =
- Test_rank_cost =
- Score = table[Test_rank_cost] + table[Test_ranking_event]
- § TOTAL RANKING=VAR FilteredTable =FILTER(ALLSELECTED(table[channel]),[€ Total cost] > 0 // Exclude zero-cost rows)RETURNIF([# Channel_count_2025] <> BLANK(), CALCULATE(IF([€ Total cost] = 0,"EXCLUDED",RANKX(FilteredTable, [Score],, ASC))))
Hi, thanks for your hint, I would like to close this querstion, I created a new one with sample data and better format.
5 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi, thanks for your hint, I would like to close this querstion, I created a new one with sample data and better format. But I don't know how to close it. Could you please help to close it?
Hi Touliloop ,
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Hi, thanks for your hint, I would like to close this querstion, I created a new one with sample data and better format.
Hi Touliloop ,
To close the thread, click the three dots at the top right of the reply as shown below. Then, select the option to accept as solution.
Thank you.
