Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
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.
Regards,
Frank
Hi @VincentC,
Does that make sense? If so , could you please mark this answer as a solution?
Regards,
Frank
User | Count |
---|---|
85 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
101 | |
52 | |
41 | |
39 | |
37 |