Forum Discussion

Vish24's avatar
Vish24
Icon for Helper II rankHelper II
6 years ago
Solved

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 ...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    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.

     

JarroVGIT's avatar
JarroVGIT
Icon for Resident Rockstar rankResident Rockstar
6 years ago

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! 🙂