Forum Discussion

rashmi_panicker's avatar
rashmi_panicker
Frequent Visitor
4 years ago
Solved

Nested Top N using Power BI DAX

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.
 
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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

     

12 Replies