Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
As per the image below, I would like to create a calculated column (Rankx) to rank the top products at customer level. By doing so, I would then be able to create a calculated column ("Top N + Other") to show the top 3 product name and the rest to be grouped as "Other".
Does anyone know how to apply the RANKX formula to create a calculated column to rank products at customer level? I tried applying the RANKX formula but it seems to only rank by product level on overall basis instead of at customer level.
Thank you!
Solved! Go to Solution.
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
Try this to add a Rankx column:
Rankx =
RANKX (
FILTER ( ALL ( 'Table' ), [Customer] = EARLIER ( 'Table'[Customer] ) ),
[Sales],
,
DESC,
DENSE
)
then this to add Top N + Other column:
Top N + Other =
if([Rankx]<=3,[Product],"Other")
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
thanks for your advise. However, the RANKX formula doesn't work. It came out as all "1". Basically I would like to know the top 3 products for each of the customers and the rest will be grouped as "Others" for each customer. How should I get around with that?
Thank you!
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 |
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |