Forum Discussion
FreedJustine
6 years agoHelper I
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 ...
- 6 years ago
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.
parry2k
6 years agoSuper User
FreedJustine what you mean rank by location sales size and total sales for the period
you wan to rank each sales person in the location by the sales, or each location in the sale person by sales?