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.
Anonymous , You are using date in visual, and rank will distribute inside date.
In your, measure remove the date or use that in rank too
Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA
amitchandak The dates should be there in the visual.
But the ranking should be done by the total sales of each customer for all dates.
Please find the Pbix file for your refernce.
https://1drv.ms/u/s!AmauTLNmHKexhGcs7UQE-SoJtnTZ
Anonymous Try if the two measures can help
M1= CALCULATE(SUM('Table'[Sales]),,filter(allseleced('Table'),'Table'[Customer] = max('Table'[Customer]) && 'Table'[Customer Group] =max('Table'[Customer Group] )))
Top 5 Customers =
RANKX(
ALL('Table'[Customer],'Table'[Customer Group], 'Table'[Date]),
[M1]
,,
DESC
)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.
amitchandak below is the expected result that i am looking for.
Please help.
Begin of Week Customer Total Sales Top 5 Expected Ranking Rule 03-01-2022 C1 8615 10 5 Part of Tier1 customegroup 10-01-2022 C1 8615 10 5 Part of Tier1 customegroup 17-01-2022 C1 8615 10 5 Part of Tier1 customegroup 24-01-2022 C1 8615 10 5 Part of Tier1 customegroup 31-01-2022 C1 8615 10 5 Part of Tier1 customegroup 03-01-2022 C2 9918 9 4 Part of Tier1 customegroup 10-01-2022 C2 9918 9 4 Part of Tier1 customegroup 17-01-2022 C2 9918 9 4 Part of Tier1 customegroup 24-01-2022 C2 9918 9 4 Part of Tier1 customegroup 31-01-2022 C2 9918 9 4 Part of Tier1 customegroup 03-01-2022 C5 10362 8 3 Part of Tier1 customegroup 10-01-2022 C5 10362 8 3 Part of Tier1 customegroup 17-01-2022 C5 10362 8 3 Part of Tier1 customegroup 24-01-2022 C5 10362 8 3 Part of Tier1 customegroup 31-01-2022 C5 10362 8 3 Part of Tier1 customegroup 03-01-2022 C9 10781 7 5 Part of Tier2 customegroup 10-01-2022 C9 10781 7 5 Part of Tier2 customegroup 17-01-2022 C9 10781 7 5 Part of Tier2 customegroup 24-01-2022 C9 10781 7 5 Part of Tier2 customegroup 31-01-2022 C9 10781 7 5 Part of Tier2 customegroup 03-01-2022 C4 10812 6 2 Part of Tier1 customegroup 10-01-2022 C4 10812 6 2 Part of Tier1 customegroup 17-01-2022 C4 10812 6 2 Part of Tier1 customegroup 24-01-2022 C4 10812 6 2 Part of Tier1 customegroup 31-01-2022 C4 10812 6 2 Part of Tier1 customegroup 03-01-2022 C3 11144 5 1 Part of Tier1 customegroup 10-01-2022 C3 11144 5 1 Part of Tier1 customegroup 17-01-2022 C3 11144 5 1 Part of Tier1 customegroup 24-01-2022 C3 11144 5 1 Part of Tier1 customegroup 31-01-2022 C3 11144 5 1 Part of Tier1 customegroup 03-01-2022 C7 11541 4 4 Part of Tier2 customegroup 10-01-2022 C7 11541 4 4 Part of Tier2 customegroup 17-01-2022 C7 11541 4 4 Part of Tier2 customegroup 24-01-2022 C7 11541 4 4 Part of Tier2 customegroup 31-01-2022 C7 11541 4 4 Part of Tier2 customegroup 03-01-2022 C10 11609 3 3 Part of Tier2 customegroup 10-01-2022 C10 11609 3 3 Part of Tier2 customegroup 17-01-2022 C10 11609 3 3 Part of Tier2 customegroup 24-01-2022 C10 11609 3 3 Part of Tier2 customegroup 31-01-2022 C10 11609 3 3 Part of Tier2 customegroup 03-01-2022 C6 11975 2 2 Part of Tier2 customegroup 10-01-2022 C6 11975 2 2 Part of Tier2 customegroup 17-01-2022 C6 11975 2 2 Part of Tier2 customegroup 24-01-2022 C6 11975 2 2 Part of Tier2 customegroup 31-01-2022 C6 11975 2 2 Part of Tier2 customegroup 03-01-2022 C8 12756 1 1 Part of Tier2 customegroup 10-01-2022 C8 12756 1 1 Part of Tier2 customegroup 17-01-2022 C8 12756 1 1 Part of Tier2 customegroup 24-01-2022 C8 12756 1 1 Part of Tier2 customegroup 31-01-2022 C8 12756 1 1 Part of Tier2 customegroup