topic Re: Rank within Category but ignore subcategory in DAX Commands and Tips

Rank within Category but ignore subcategory

shep123 — Fri, 18 Dec 2020 18:06:37 GMT

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)

Re: Rank within Category but ignore subcategory

Fowmy — Fri, 18 Dec 2020 18:31:06 GMT

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

Re: Rank within Category but ignore subcategory

shep123 — Fri, 18 Dec 2020 18:53:12 GMT

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

Re: Rank within Category but ignore subcategory

shep123 — Fri, 18 Dec 2020 19:34:42 GMT

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.

Re: Rank within Category but ignore subcategory

Anonymous — Fri, 04 Jun 2021 12:48:54 GMT

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!

Re: Rank within Category but ignore subcategory

Anonymous — Mon, 04 Apr 2022 13:23:40 GMT

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!