Nested Top N using Power BI DAX

[rashmi_panicker]

Hello All,

I am working on a Power BI requirement where I have Sales for say 10 categories and within each category I have approximately 15-20 products. The requirement is to display 5 bar graphs for each of the top 5 categories and within each bar graph, I have to display top 10 products. I tried Ranking following the the example here - https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

But, when I try to filter the rank column, I don't get proper result.

Also, I tried using the Top N for the Top 10 products but not sure how to come up with a nested top N where I can display data separately like Top 10 products for top 1st category then Top 10 products for top 2nd category and so on till Top 10 products for top 5th category.

Please let me know if anyone has worked on similar requirement.

Thanks.

[amitchandak]

@rashmi_panicker , refer if this blog can help

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

[rashmi_panicker]

Thank you for your response Amit. But, I have already tried this example and have mentioned the same url in my post above. I am able to reach to a point where I am able to rank my Categories and Products properly using this sqlbi.com post. But, when I try to add filter on the Rank column, I don't get the required data.

[v-janeyg-msft]

Hi, @rashmi_panicker 

I don't know what your formulas and data look like, it's hard to give you substantial help.

Can you share some sample data and your desired result? So we can help you soon.

Janey

[rashmi_panicker]

In the above figure, I am getting the Rank Multi values using the following  measure. But, I am not able to filter my visual for the 2nd highest Category. This is because if I filter the visual by Rank Multi = 2, it will give me 2nd product from all the categories. I think, if we can somehow bring the values of Rank Multi as entered manually in the pic above, I can filter the categories and display each category in separate visual

Sales Unfiltered =

IF (

    ISINSCOPE ( 'Product'[Product Name] ),

    [Sales Amount],

    CALCULATE (

        [Sales Amount],

        ALL ( 'Product'[Product Name] )

    )

)

Rank Multi =

IF (

    ISINSCOPE( 'Product'[Product Name] ),

        RANKX (

        CALCULATETABLE (

            VALUES ( 'Product'[Product Name] ),

            ALLSELECTED ( 'Product'[Product Name] )

        ),

        [Sales Amount]

    ),

    IF (

        ISINSCOPE( 'Product'[Category] ),

        RANKX (

            ALLSELECTED ( 'Product'[Category] ),

            [Sales Unfiltered]

        )

    )

)

[v-janeyg-msft]

Hi,  @rashmi_panicker 

I have understood your needs. As for your problem, it is because the value of measure will change with the context, and the context of the matrix also involves hierarchy, which is more complex. But measure has no hierarchy in bar chart.

If you can provide a data sample, I can test it for you. According to your needs ,It may need to modify measure or use column. Creating data out of thin air is not my thing.

Reference:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Janey

[rashmi_panicker]

Hello Janey,

I am not able to find the option for attaching the source file. PFB the data.

Manufacturer	Category	Sales	Date
Alpha	C1	15100	11-01-2022
Alpha	C2	13800	11-01-2022
Alpha	C3	16300	12-01-2022
Alpha	C4	14400	13-01-2022
Alpha	C5	18200	06-01-2022
Beta	C9	15600	19-01-2022
Beta	C10	14600	20-01-2022
Beta	C6	12900	07-01-2022
Beta	C1	13700	08-01-2022
Beta	C8	18900	07-01-2022
Gamma	C11	18300	21-01-2022
Gamma	C8	19100	11-01-2022
Gamma	C13	13900	11-01-2022
Gamma	C14	12100	12-01-2022
Gamma	C15	12800	13-01-2022
Theta	C20	14800	19-01-2022
Theta	C16	19900	06-01-2022
Theta	C17	18700	07-01-2022
Theta	C18	16500	08-01-2022
Theta	C19	13700	07-01-2022

For the above data,

Totals for Manufacturers
Alpha	77800
Beta	75700
Gamma	76200
Theta	83600

Based on the above Total, Theta, Alpha and Gamma are the top 3 Manufacturers.

So, I want 3 graphs on my report for the top 3 Manufacturers. Each showing top 4 categories,

For Theta, C16, C17, C18 & C20 should be displayed on the 1st graph

For Alpha, C5, C3, C1 & C4 should be displayed on the 2nd graph

For Gamma, C8, C11, C13 & C15 should be displayed on the 3rd graph

Thank you.

[v-janeyg-msft · Accepted solution]

Hi, @rashmi_panicker 

Because the context of the matrix is complex, and the value of measure changes according to the results in the filter pane, it is not possible to simply filter.

With my efforts, I made this perfect solution, hope you can give me a kudos.

Total rank measure:

Rank = 
IF (

    ISINSCOPE ( 'Table'[Category] ),
        RANKX (
            FILTER (
                ALL ( 'Table' ),
                [Manufacturer] = SELECTEDVALUE ( 'Table'[Manufacturer] )
            ),
            CALCULATE ( MAX ( 'Table'[Sales] ) )
        )
    
    ,
    IF (
            ISINSCOPE ( 'Table'[Manufacturer] ),
    RANKX (
        SUMMARIZE ( ALL ( 'Table' ), [Manufacturer], "total", SUM ( 'Table'[Sales] ) ),
        CALCULATE ( SUM ( 'Table'[Sales] ) ),
))
        
)

Column for rank category:

rankcategory = RANKX(FILTER(ALL('Table'),[Manufacturer]=EARLIER('Table'[Manufacturer])),[Sales])

I created a column because I can't use two topns in the filter pane.

Below is my sample. If you don't understand anything, feel free to ask me.

Janey

[rashmi_panicker]

Thank you Janey for your response. The solution that you provided is perfect but I am not able to go beyond this point. We are able to get the result in a Matrix. Now, how do we split these 3 Manufacturers on 3 different graphs displaying their own respective top 4 categories and their sales?

[v-janeyg-msft]

Hi, @rashmi_panicker 

Create two columns for rank and use it in filter pane. Set the rankmanufacturer to 1, 2, and 3 in the three graphs respectively.

rankManufacturer = RANKX (
       SUMMARIZE ( ALL ( 'Table' ), [Manufacturer], "total", SUM ( 'Table'[Sales] ) ),
       SUMX(FILTER('Table',[Manufacturer]=EARLIER('Table'[Manufacturer])),[Sales]))

Janey

[rashmi_panicker]

Hello Janey,

Your solution will definitely work in case of the sample shared by me. But, my actual table has many other columns and my report has multiple slicers. When I try to add a column in this table for rank, I am getting the result as 3 & 4 digit numbers instead of 1,2,3, etc. How do I modify the DAX expression for rank column to incorporate this scenario?

[v-janeyg-msft]

Hi, @rashmi_panicker 

Due to your special needs, I can only use calculated column, because it doesn't change with the context like measure, but requires more memory. 

What's mean about  'I am getting the result as 3 & 4 digit numbers instead of 1,2,3, etc'? I don't know your real scenario or how you modified my code in your report, how else can I help you?

Janey

[v-janeyg-msft]

@rashmi_panicker  I'm too busy today, I'll reply to you next week. Sorry.