Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculated column RANKX with multiple columns

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.

 

Spacewalker_0-1637068074508.png

Thank you!

 

1 ACCEPTED 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:

VahidDM_0-1637231531941.png

 

 

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/

 

 

View solution in original post

7 REPLIES 7
VahidDM
Super User
Super User

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/

 

 

Anonymous
Not applicable

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/

 

 

 

Anonymous
Not applicable

Hi @VahidDM 

Here you go!

 

CustomerProductSales
AllyProduct A           1,000
AllyProduct D               200
AllyProduct A           1,000
AllyProduct B               300
AllyProduct B               300
AllyProduct C                 50
JasonProduct A           1,000
JasonProduct A           1,000
JasonProduct A           1,000
JasonProduct A           1,000
JasonProduct B               200
JasonProduct C               100
JasonProduct D                 60

 

Below is the screenshot of outcome in PowerBI:

Spacewalker_0-1637228632197.png

 

The outcome i desire is as below:

Spacewalker_1-1637228765092.png

 

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:

VahidDM_0-1637231531941.png

 

 

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/

 

 

Anonymous
Not applicable

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!😀

Anonymous
Not applicable

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!

 

 

CustomerProductMonth CategorySales
AllyProduct AM-1           1,000
AllyProduct DM-1               200
AllyProduct AM-2           1,000
AllyProduct BM-2               300
AllyProduct BM-3               300
AllyProduct CM-4                 50
JasonProduct AM-2           1,000
JasonProduct AM-2           1,000
JasonProduct AM-3           1,000
JasonProduct AM-3           1,000
JasonProduct BM-4               200
JasonProduct CM-4               100
JasonProduct DM-5                 60

 

Spacewalker_0-1637578070498.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors