cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arielcedola Helper II
Helper II

Ranking of items in 3 subcategories

Hi,

I need some measurements to get the ranking of the different items within some subcategories (Area/Product/Article), and also the top ranked item in each ranking. The ranking in a given subcategory must be filtered by the top ranked item in the upper one.

This is the sample dataset. There are 2 items in Area, 4 Products within each Area, and 3 different Articles for each Product:

 

table shops3.png

For example, if the top ranked Area is Audio, I need to know the top ranked audio Product (from Headphones, Hi-Fi, Mic, MP3), and for this product (let's say for example Headphones) the top ranked article (from Headph_1, Headph_2 and Headph_3 in this case).

I have to show the 3 rankings in 3 separate tables or matrices, and the top ranked items in cards.

Area, Product and Article must be ranked by sum of ArticleNSales, ProductMeanProfit and ArticleNSales, respectively.

Here the link to the file, thanks in advance!

https://drive.google.com/file/d/1a1JiGPPiPrqn_1zTMOJbYe7YaGBWRK8r/view?usp=sharing

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV
Super User IV

Re: Ranking of items in 3 subcategories

Creating the right measures is not as simple as one might initially think.

 

Please find a solution attached.

 

Best

D



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


View solution in original post

mahoneypat Community Champion
Community Champion

Re: Ranking of items in 3 subcategories

I ran out of time but did figure out an updated Product Rank measure.  This one will return blank for any products that are not in the top area.  The same approach could be used for an updated Article measure too.  I see another solution is posted, so let me know if you still need that measure (or if this one doesn't meet the need).

 

Product Rank Top Area =
VAR areatotal =
CALCULATE (
SUM ( Sales[ArticleNSales] ),
ALL ( Sales[Product] ),
VALUES ( Sales[Area] )
)
VAR arearank =
COUNTROWS (
FILTER (
ALL ( Sales[Area] ),
CALCULATE ( SUM ( Sales[ArticleNSales] ), ALL ( Sales[Product] ) ) >= areatotal
)
)
RETURN
IF (
arearank = 1,
RANKX (
ALL ( Sales[Product] ),
CALCULATE (
SUM ( Sales[ProductMeanProfit] ),
FILTER ( VALUES ( Sales[Area] ), [Area Rank] = 1 )
),
,
DESC,
DENSE
),
BLANK ()
)

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

View solution in original post

4 REPLIES 4
mahoneypat Community Champion
Community Champion

Re: Ranking of items in 3 subcategories

These three measures seem to do the trick in your pbix file - 

 

Area Rank = RANKX(ALLSELECTED(Sales[Area]), CALCULATE(SUM(Sales[ArticleNSales])),,DESC,Dense)
 
Product Rank = RANKX(All(Sales[Product]),CALCULATE(SUM(Sales[ProductMeanProfit]), VALUES(Sales[Area])),,DESC,Dense)
 
Article Rank = RANKX(All(Sales[Article]),CALCULATE(SUM(Sales[ArticleNSales]), VALUES(Sales[Product]), VALUES(Sales[Area])),,DESC,Dense)
 
Hadn't done that before, and learned something new thing.  Thanks.
 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Highlighted
arielcedola Helper II
Helper II

Re: Ranking of items in 3 subcategories

Hi @mahoneypat 

Thanks a lot for your response!

There is still a little step missing. The measures seems ok in the sense that they provide the rank separately for each subcategory, as needed. See the image:

3TABLES.png

The point is that in the second table I should see only the 4 products related to the first ranked Area: for the example in the image Headphones, Hi-Fi, MP3, MIC because the first ranked Area is AUDIO. And in the third table I should see just the 3 articles associated to the first ranked Product: in this case HEADPH_1, HEADPH_2, HEADPH_3.

I tried with the Top N visual filter, but I would need 2 of them in the Articles table and it is not allowed. There should be some solution using DAX. 

And finally, how can I get the 3 first ranked items (Area, Product and Article) in three different cards, as explained in the original question?

Thanks again for your time!

Super User IV
Super User IV

Re: Ranking of items in 3 subcategories

Creating the right measures is not as simple as one might initially think.

 

Please find a solution attached.

 

Best

D



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


View solution in original post

mahoneypat Community Champion
Community Champion

Re: Ranking of items in 3 subcategories

I ran out of time but did figure out an updated Product Rank measure.  This one will return blank for any products that are not in the top area.  The same approach could be used for an updated Article measure too.  I see another solution is posted, so let me know if you still need that measure (or if this one doesn't meet the need).

 

Product Rank Top Area =
VAR areatotal =
CALCULATE (
SUM ( Sales[ArticleNSales] ),
ALL ( Sales[Product] ),
VALUES ( Sales[Area] )
)
VAR arearank =
COUNTROWS (
FILTER (
ALL ( Sales[Area] ),
CALCULATE ( SUM ( Sales[ArticleNSales] ), ALL ( Sales[Product] ) ) >= areatotal
)
)
RETURN
IF (
arearank = 1,
RANKX (
ALL ( Sales[Product] ),
CALCULATE (
SUM ( Sales[ProductMeanProfit] ),
FILTER ( VALUES ( Sales[Area] ), [Area Rank] = 1 )
),
,
DESC,
DENSE
),
BLANK ()
)

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.