Rank within Category but ignore subcategory

[shep123]

I am trying to create a rank measure within an specific categories but ignore others. I have seen a lot of similar questions asked but couldn't find one that did exactly what I needed.

Sample Data

Order Status	Order ID	Account ID	Order $	Rank (Desired)
Delivered	aaa	Cust A	100	1
Delivered	bbb	Cust B	50	2
Delivered	ccc	Cust A	30	3
Shipped	ddd	Cust C	75	1
Shipped	eee	Cust D	40	2
Received	fff	Cust B	200	1
Received	ggg	Cust E	15	2
Received	hhh	Cust A	10	3

My current formula is:

Rank = IF(HASONEVALUE('Table'[Order ID]), RANKX(ALL('Table'[Order ID]), CALCULATE(SUM('Table'[Order $]))), BLANK())

This works for all scenarios where I don't include the Account ID in the matrix but breaks once I added that attribute in. That is a necessary attribute as I want to rank by Orders within the Order Status category but allow the user to easy see the customer name as well.

Sample File (Dropbox) 

[hrishi]

Hello, 

Could you please guide me on this issue.
I tried the same way of creating the measure and the measure will work when you have one category however as soon as you add another category (sub category) the ranking changes. 

Is there any way to define global ranking on a category?

Looking forward to your response 🙂 

Thank you!

[Jolanda18]

Hi all, I have a similar issue only the formula presented does not solve it. Let me explain my situation, hopefully you are able to help me out.

 I have a table like this (much bigger table actually but as an example)

 Table 1                                                              

Customer  Country              Country Group     Sales

ABC         Netherlands        Benelux                100

ABC         Belgium              Benelux                50

DEF          Netherlands        Benelux                200

GHI          Netherlands        Benelux                75

JKL           Belgium              Benelux                20

 and I was using the formula to rank them:Customer Rank TO = RANKX(ALL('table1'[Customer]), CALCULATE(SUM('table1'[Sales]),allexcept('table1','table1'[Customer]),,DESC,Dense)

 when plotting this in a matrix, it works fine:

Customer  Sales                  Customer Rank TO

DEF          200                    1

ABC         150                    2

GHI          75                      3

JKL           20                      4

 And I can show the top 2 by setting a threshold of 2

[Customer Rank TO]<=2

Customer  Sales                  Customer Rank TO

DEF          200                    1

ABC         150                    2

 However, when I bring in country into the matrix, it does not keep the ranking on total sales by customer anymore but takes a customer-country perspective and I get more results than 2. This happens because the logic looks at the top 2 of the individual countries. JKL is in the top 2 of Belgium (but not a top 2 of Benelux). I want it to keep looking to the overall ranking and not by country. How to solve this? 

I tried the formula: Customer Rank TO = RANKX(ALLexcept('table1','table1'[Customer]), CALCULATE(SUM('table1'[Sales])),,DESC,Dense) but I then don't get results (not enough memory). Any suggestion on how I can solve? 

Thanks for your kind help!

[shep123]

Thank you in everything I tried I somehow didn't that solution

[shep123]

How would I adjust this formula if I wanted to be dynamic from another filter like order month for example? Based on my testing the ranks stay the same even if I filter out certain orders based on what was ordered that month.

[Fowmy · Accepted solution]

@shep123 

Use the following measure to calculate the rank as desired:

Rank = 

RANKX(
    ALLEXCEPT(Table,Table[Order Status]),
    CALCULATE(SUM(Table[Order $]))
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn