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

[Syndicate_Admin]

Hello

I need some measurements to get the ranking of the different items within some subcategories (area/product/item), and also the top ranked item in each ranking. The rank of a given subcategory should be filtered by the highest ranked item at the top.

Here's the sample dataset. There are 2 items in the area, 4 products within each area and 3 different items for each product:

For example, if the highest ranked area is Audio, I need to know the highest ranked audio product (headphones, hi-fi, microphone, MP3), and for this product (say for example headphones) the highest ranked item (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 on the cards.

Area, Product, and Article must be sorted by the 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

[v-yingjl · Accepted solution]

Hi @arielcedola ,

You can create the following measures:

Rank Area = RANKX( ALLSELECTED(Sales[Area]), CALCULATE(SUM(Sales[ArticleNSales])),,DESC,Dense)

Rank Product = 
VAR a =
    CALCULATETABLE(FILTER ( DISTINCT ( 'Sales'[Area] ), [Rank Area] = 1 ),ALLSELECTED())
RETURN
    IF (
        CALCULATE ( COUNTROWS ( 'Sales' ), 'Sales'[Area] IN a ) = 0,
        BLANK (),
        RANKX (
            GROUPBY (
                FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[Area] IN a ),
                'Sales'[Product]
            ),
            CALCULATE ( SUM ( 'Sales'[ProductMeanProfit] ), 'Sales'[Area] IN a ),
            ,
            DESC,
            DENSE
        )
    )

Rank Article = 
VAR a =
    CALCULATETABLE(FILTER ( DISTINCT ( 'Sales'[Product] ), [Rank Product] = 1 ),ALLSELECTED())
RETURN
    IF (
        CALCULATE ( COUNTROWS ( 'Sales' ), 'Sales'[Product] IN a ) = 0,
        BLANK (),
        RANKX (
            GROUPBY (
                FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[Product] IN a ),
                'Sales'[Article]
            ),
            CALCULATE ( SUM ( 'Sales'[ArticleNSales] ), 'Sales'[Product] IN a ),
            ,
            DESC,
            DENSE
        )
    )

In table 2 and table 3, set the value of second and third measure is not blank in the table visual filter and you will get your expected result:

Sample file is attached that hopes to help you, please check and try it: Ranking of items in 3 subcategories.pbix 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[arielcedola]

Hi @v-yingjl 

Great solution, thanks a lot!

Best 😉

[harshnathani]

Hi @arielcedola ,

You can create the following measures.

Rank Area =
RANKX (
    ALLSELECTED ( Sales[Area] ),
    CALCULATE (
        SUM ( Sales[ArticleNSales] )
    ),
    ,
    DESC
)

Rank Article = RANKX( ALLSELECTED(Sales[Article]), CALCULATE(SUM(Sales[ArticleNSales])),,DESC)

Rank Product = RANKX( ALLSELECTED(Sales[Product]), CALCULATE(SUM(Sales[ProductMeanProfit])),,DESC)

Please find pbix attached :

https://drive.google.com/open?id=1lVIFoxJkBlTMGPPbyF7-4AQY7-zPXME6

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[arielcedola]

Hi @harshnathani 
thanks a lot for your quick response!
There is still something to adjust, because as I exposed in the question I need to rank each subcategory filtering by the top ranked item in the category above.
If you check the file you sent me, for example for Shop A the top Area is Video but the resulting top Product belongs to Audio (Headphones). Actually in the second table just the 4 products belonging to Video should be ranked (because Video is the top ranked Area), whereas in the third ranking just the 3 articles belonging to the top ranked Product should appear.
This filtering should be introduced in the DAX formulas (not in the visual level filter of the tables).
Thanks again!

[amitchandak]

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...