Forum Discussion
Ranking based on measure
Hello,
I am trying to do the ranking for sales by people in regions. I am getting correct result if I am showing in table. But I want to make a slicer for a sales persons names and then show the ranking of the selected person . I am getting one in ranking for each person. Its giving me correct result if I am making a table with names and rank.
Please
Hi Vish24 ,
At first, you need to add an index coloumn in the query editor.
Then refer to the following measure:
Measure = MINX ( FILTER ( SELECTCOLUMNS ( ALLSELECTED ( 'Table' ), "index", 'Table'[Index], "rank", RANKX ( ALLSELECTED ( 'Table' ), 'Table'[Column2],, DESC, DENSE ) ), [index] = MAX ( 'Table'[Index] ) ), [rank] )Here is my test file for your reference.
4 Replies
Refer this :https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
Also in this blog, I filter on top brand. Refer :Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on LinkedinI want to show the ranking only for the selected person.
Hi Vish24 ,
At first, you need to add an index coloumn in the query editor.
Then refer to the following measure:
Measure = MINX ( FILTER ( SELECTCOLUMNS ( ALLSELECTED ( 'Table' ), "index", 'Table'[Index], "rank", RANKX ( ALLSELECTED ( 'Table' ), 'Table'[Column2],, DESC, DENSE ) ), [index] = MAX ( 'Table'[Index] ) ), [rank] )Here is my test file for your reference.
In your measure, change RANKX(<table>.... to RANKX(ALL(<Table>)...
This is likely to solve your case, depending on your datamodel. Otherwise your measure would be something like this:
RankingMeasure = VAR _tmpTable = SUMMARIZE(Sales, Sales[SalesPerson], "totalSales", SUM(Sales[Amount])) VAR _rankedTable = ADDCOLUMNS(_tmpTable, "rank", RANKX(_tmpTable, [totalSales], , DESC) VAR _curSalesPerson = SELECTEDVALUE(Sales[SalesPerson]) RETURN MAXX(FILTER(_rankedTable, [SalesPerson] = _curSalesPerson), [rank])This is without any intellisense so forgive any errors or typos but is does illustrate the required logic 🙂 Let me know if this helps you!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
