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 Anonymous
Can you copy and past a sample of your data table here (in table format to be able to copy and past that)?
Those Codes in my previous post are need to be used to add new columns.
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