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

 

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