Forum Discussion
Calculated column RANKX with multiple columns
Hi Anonymous
The problem is because of that change you did in the code, remove that SUM from line 4 in the code (based on the screenshot you sent)
try this and don't change anything (copy past):
Rankx = RANKX ( FILTER ( ALL ( 'Sales' ), [Customer] = EARLIER ( 'Sales'[Customer] ) ), [Sales], , DESC, DENSE )Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi VahidDM
Here you go!
| Customer | Product | Sales |
| Ally | Product A | 1,000 |
| Ally | Product D | 200 |
| Ally | Product A | 1,000 |
| Ally | Product B | 300 |
| Ally | Product B | 300 |
| Ally | Product C | 50 |
| Jason | Product A | 1,000 |
| Jason | Product A | 1,000 |
| Jason | Product A | 1,000 |
| Jason | Product A | 1,000 |
| Jason | Product B | 200 |
| Jason | Product C | 100 |
| Jason | Product D | 60 |
Below is the screenshot of outcome in PowerBI:
The outcome i desire is as below:
Appreciate your help as i am very new to PowerBI and DAX!
Thank you!!
Hi Anonymous
The problem is because of that change you did in the code, remove that SUM from line 4 in the code (based on the screenshot you sent)
try this and don't change anything (copy past):
Rankx =
RANKX (
FILTER ( ALL ( 'Sales' ), [Customer] = EARLIER ( 'Sales'[Customer] ) ),
[Sales],
,
DESC,
DENSE
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi VahidDM
It works!! You have helped to save my potential endless hours on trying to solve this!
I will go ahead and apply the same to my real case and hopefully it does not turn complicated!
Thank you so much!! Appreciate it!😀
Hi VahidDM
Sorry to bother you again. I have additional question on the above topic.I use the Summarize function to create a table to summarise customer, product, sales from another data table, before I rankx them.
If I want to add additional columns such as "Month Category" in my summarised table, how will it change the RANKX formula above? I want to achieve the same result as before, i.e. rank top products within customer group, regardless of month category. The month category is for other calculation purpose.
I tried to use the sample dataset below, it works but when i apply it to my real case scenario, the RANKX does not seem to work. The RankX was affected by the month category. I am not sure which part I am doing wrong. Please see the second screenshot below (based on real case scenario but confidential info is modified).Appreciate your help! Thank you!
Customer Product Month Category Sales Ally Product A M-1 1,000 Ally Product D M-1 200 Ally Product A M-2 1,000 Ally Product B M-2 300 Ally Product B M-3 300 Ally Product C M-4 50 Jason Product A M-2 1,000 Jason Product A M-2 1,000 Jason Product A M-3 1,000 Jason Product A M-3 1,000 Jason Product B M-4 200 Jason Product C M-4 100 Jason Product D M-5 60