Ranking of items in 3 subcategories

[arielcedola]

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:

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

[Anonymous · Accepted solution]

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

Please find a solution attached.

Best

D

[mahoneypat]

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

[arielcedola]

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:

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!

[mahoneypat · Accepted solution]

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

[Anonymous · Accepted solution]

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

Please find a solution attached.

Best

D