Forum Discussion
Rank using 2 measures
Hi , FreedJustine
Here is a demo.
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.
Hi , FreedJustine
Here is a demo.
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.
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 1 | Group 2 | Group 3 |
| BA | North | South |
| West | North-East | South-East |
| East | North-West | South-West |
| HO |
Requirement to determine the locations sales size:
| Average Monthly Sales requirement | |||
| Group | Small (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.