Forum Discussion

FreedJustine's avatar
FreedJustine
Helper I
6 years ago
Solved

Rank using 2 measures

Hi! Need Help!   I want to create a rank measure, using 2 measures, not columns. Rankx seems to work  for columns in tables.   I want to rank the sales representative base on location sales size ...
Need Help
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , FreedJustine 

    Here is a demo.

    Pbix attached 

    If help ,please follow these  step.

    1.Create measure 'Rank1'as below

    Rank 1 = 
SWITCH(TRUE(),
[Average Sales per Month]<2000000,1,
[Average Sales per Month]>4000000,3,
[Average Sales per Month]>=2000000 && [Average Sales per Month] <=4000000,2)

     

    2.Create measure "RankValue" as below:

    rankvalue =
RANKX (
    GROUPBY ( ALLSELECTED ( 'Sales' ), Sales[Location], Sales[Sales Rep] ),
    CALCULATE ( [Rank 1] )
        + RANKX (
            GROUPBY ( ALLSELECTED ( 'Sales' ), Sales[Location], Sales[Sales Rep] ),
            CALCULATE ( SUM ( 'Sales'[Total Sales] ) ),
            ,
            ASC,
            DENSE
        )
            / CALCULATE ( COUNTROWS ( 'Sales' ), ALLSELECTED () ),
    ,
    DESC,
    DENSE
)

    3. It will show as below

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.