Forum Discussion
DAX: Ranking a measure based on a Column
Hi, Anonymous ;
Try it.
Total Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),[Customer Group]=MAX('Table'[Customer Group])&&[Customer]=MAX('Table'[Customer])))Top 5 Customers = RANKX( FILTER( ALL('Table'),[Tier1/2]=MAX('Table'[Tier1/2])), CALCULATE( [Total Sales]) ,, DESC,Dense)The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the help amitchandak
Please check the question as i have updated the expected result.
But it is not working as expected.
Its returning the ranks for total sales but not as per the Customer groups.
If you see the dax which i tried, giving the ranking for the Tier2 groups properly, but not to the Tier1 groups.
Total Sales = CALCULATE(SUM('Table'[Sales]),ALLSELECTED('Table'[Begin of Week]))
Top 5 Customers =
RANKX(
ALL('Table'[Customer]),
CALCULATE(
'Table'[Total Sales],
ALLEXCEPT('Table','Table'[Customer],'Table'[Customer Group])
)
,,
DESC
)
Two issues with the above query
1 - It gives the same ranks for tier1 customers - should give the ranks for these customers as per the total sales values in desc.
2 - When i remove the Customergroup column from the visual. ranks are given as per the total sales, but it should break as per the customer group.
Need your help amitchandak
Thanks,
Mohan V.
amitchandak Ashish_Mathur Could you please help me with this.