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.

v-easonf-msft's avatar
v-easonf-msft
Community Support
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.

  • FreedJustine's avatar
    FreedJustine
    Helper I
    6 years ago

    v-easonf-msft  

     

    Thank you very much! this the result im looking for. However i have additional problem, average monthly sales now is base on pre determine values per locations group.

     

    Locations Group:

    Group 1Group 2Group 3
    BANorthSouth
    WestNorth-EastSouth-East
    EastNorth-WestSouth-West
    HO  

     

    Requirement to determine the locations sales size:

     Average Monthly Sales requirement
    GroupSmall (1)Medium (2)Large (3)
    Group 1<2000000>=2000000 <=4000000>4000000
    Group 2<1000000>=1000000 <=5000000>5000000
    Group 3<1000000>=1000000 <=3000000>3000000

     

    I was thinking if i can apply in 

    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)

     When I try to add the group its not working.