topic Re: Calculated column RANKX with multiple columns in DAX Commands and Tips

Calculated column RANKX with multiple columns

Anonymous — Thu, 18 Nov 2021 09:47:25 GMT

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!

Re: Calculated column RANKX with multiple columns

VahidDM — Tue, 16 Nov 2021 22:07:00 GMT

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/

Re: Calculated column RANKX with multiple columns

Anonymous — Thu, 18 Nov 2021 07:06:58 GMT

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!

Re: Calculated column RANKX with multiple columns

VahidDM — Thu, 18 Nov 2021 07:20:05 GMT

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/

Re: Calculated column RANKX with multiple columns

Anonymous — Thu, 18 Nov 2021 09:50:42 GMT

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

Re: Calculated column RANKX with multiple columns

VahidDM — Thu, 18 Nov 2021 10:32:21 GMT

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/

Re: Calculated column RANKX with multiple columns

Anonymous — Thu, 18 Nov 2021 10:58:47 GMT

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

Re: Calculated column RANKX with multiple columns

Anonymous — Mon, 22 Nov 2021 10:51:17 GMT

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