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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
VincentC
Frequent Visitor

DAX Rank of the category for subcategory

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

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

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.

1.png

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @VincentC,

 

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

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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