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...
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

     

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
4 years ago

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's avatar
rashmi_panicker
Frequent Visitor
4 years ago

Hello Janey,

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

ManufacturerCategorySalesDate
AlphaC11510011-01-2022
AlphaC21380011-01-2022
AlphaC31630012-01-2022
AlphaC41440013-01-2022
AlphaC51820006-01-2022
BetaC91560019-01-2022
BetaC101460020-01-2022
BetaC61290007-01-2022
BetaC11370008-01-2022
BetaC81890007-01-2022
GammaC111830021-01-2022
GammaC81910011-01-2022
GammaC131390011-01-2022
GammaC141210012-01-2022
GammaC151280013-01-2022
ThetaC201480019-01-2022
ThetaC161990006-01-2022
ThetaC171870007-01-2022
ThetaC181650008-01-2022
ThetaC191370007-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's avatar
    v-janeyg-msft
    Community Support
    4 years ago

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

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    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

     

    • rashmi_panicker's avatar
      rashmi_panicker
      Frequent Visitor
      4 years ago

      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's avatar
        v-janeyg-msft
        Community Support
        4 years ago

        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