DAX Rank of the category for subcategory

[VincentC]

Hi,

I'm trying to calculate the rank of categories of products and display this rank for the subcategories linked to each of those categories. For example, I've this dataset:

Category;Subcategory;Value
A;A1;1
A;A2;1
A;A3;1
A;A4;1
A;A5;1
A;A6;1
B;B1;9
B;B2;9
B;B3;9
B;B4;9
B;B5;9
B;B6;9
B;B7;9
B;B8;9
C;C1;3
C;C2;3
C;C3;3
C;C4;3

The rank for the categories are calculated this way (on my data I've a specific measure but here I'm trying to illustrate my problem):

RANKX(
ALLSELECTED(Table[Category]);
CALCULATE(MIN(Table[Value]))
)

This gives me the result:

Category; Rank
A; 1
B; 3
C; 2

For the subcategories, here what I'd like:

Subcategory;	Rank of the category its related
A1;	1
A2;	1
A3;	1
A4;	1
A5;	1
A6;	1
B1;	3
B2;	3
B3;	3
B4;	3
B5;	3
B6;	3
B7;	3
B8;	3
C1;	2
C2;	2
C3;	2
C4;	2

I've a slicer for selecting the value of N and a table displaying the top N categories. I'd like to display the subcategories related to those top N categories.

I'm not able to use a calculated column as I'd like it to be dynamic, the end user having to select Shop and Date to display the result.

Regards,

Vincent

[v-frfei-msft]

Hi @VincentC,

Based on my test, we can take the following steps to meet your requirement.

1. Enter the data and update your measure as below.

rank = RANKX(ALLSELECTED('Table'),CALCULATE(MIN('Table'[Value])),,ASC,Dense)

2. Then we can get the result as below.

In addation, for the top N function, is that a single table or did you create a what if parameter?

For more details, please check the PBIX as attached.

https://www.dropbox.com/s/ykyu6rhdt8srzmo/DAX%20Rank%20of%20the%20category%20for%20subcategory3.pbix...

Regards,
Frank

[v-frfei-msft]

Hi @VincentC,

Does that make sense? If so , could you please mark this answer as a solution?

Regards,
Frank