I am trying to create a Rank column (not measure) based on the total sales per customer in my dataset.
Rank column = RANKX( ALLSELECTED('Dataset'[Customer]), CALCULATE( SUM('Dataset'[Sales]), FILTER(ALLSELECTED('Dataset'), 'Dataset'[Customer] = EARLIER('Dataset'[Customer]) ) ), , DESC)
The formula worked fine until... I apply a query filter (say on the slicer on the material column).
I have tried to debug the formula quite a bit but I have the impression, that DAX cannot apply the ALLSELECTED query filter when the FILTER is nested inside the RANKX + CALCULATE functions.
What makes me think that is that the following formula works fine.
Sales by Customer = CALCULATE( SUM('Dataset'[Sales]), FILTER(ALLSELECTED('Dataset'), 'Dataset'[Customer] = MAX('Dataset'[Customer]) ) )
Thanks to all in advance!
Solved! Go to Solution.
Thank you very much for taking the time to look into my issue!
This is exactly the result I am expecting except the Rank needs to be column and not a measure.
(Because I want to apply a RunningSum based on the rank column).
Oh thanks Lydia, I missed out this key concept.
I will then think to achieve what I am looking for.
Thanks for your help.