Forum Discussion

prabhuk's avatar
prabhuk
Helper I
6 years ago
Solved

TOP N and Others

Hi All,

I am trying to have dynamic Top N and All others product Dashboard. But i am not getting desire output. Attached my file for your reference. Kinly help to correct the DAX.

 

PS. if get TopX and All others in the same colums will help.

 

https://www.dropbox.com/s/5sfhtn6hmphs7bk/Top%20N%20Product.pbix?dl=0 

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please take following steps:

    1)Create a calculated table like this:

    Table = 
UNION (
    SELECTCOLUMNS ( DATA, "Product", DATA[PRODUCT], "Rank", DATA[rank] ),
    DATATABLE ( "Product", STRING, "Rank", INTEGER, { { "Other products", 0 } } )
)

    2)Try this measure:

    Measure = 
IF (
    MAX ( 'Table'[Rank] ) >= MIN ( 'TOPN'[TOP N] )
        && MAX ( 'Table'[Rank] ) <= MAX ( 'TOPN'[TOP N] ),
    CALCULATE (
        SUM ( DATA[SALES] ),
        FILTER ( DATA, DATA[rank] = MAX ( 'Table'[Rank] ) )
    ),
    IF (
        MAX ( 'Table'[Rank] ) = 0,
        CALCULATE ( SUM ( DATA[SALES] ), ALL ( DATA ) )
            - CALCULATE (
                SUM ( DATA[SALES] ),
                FILTER (
                    DATA,
                    DATA[rank] >= MIN ( 'TOPN'[TOP N] )
                        && DATA[rank] <= MAX ( 'TOPN'[TOP N] )
                )
            )
    )
)

    3)When select values in slicer, the result shows:

    See my attached pbix file.

     

    Best Regards,

    Giotto

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Prabhu,

     

    Not able to understand your requirement very clearly.

     

    Please see the the below video for dynamic TOPN.

     

    https://www.youtube.com/watch?v=SsZseKOgrWQ

     

    Let me know if this helps.

     

    Regards,

    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

     

    • prabhuk's avatar
      prabhuk
      Helper I
      6 years ago

      Thank You,

      How to have all other products in single row. Example if we select top 5 product, first 5 row shoudl reflect the top 5 product and 6th row shold come as all other products. For all other products no need to have bifurcation and it can consolidae values of all other product and reflect in single row.

      Kindly giuide.

       

      • v-gizhi-msft's avatar
        v-gizhi-msft
        Community Support
        6 years ago

        Hi,

         

        Please take following steps:

        1)Create a calculated table like this:

        Table = 
UNION (
    SELECTCOLUMNS ( DATA, "Product", DATA[PRODUCT], "Rank", DATA[rank] ),
    DATATABLE ( "Product", STRING, "Rank", INTEGER, { { "Other products", 0 } } )
)

        2)Try this measure:

        Measure = 
IF (
    MAX ( 'Table'[Rank] ) >= MIN ( 'TOPN'[TOP N] )
        && MAX ( 'Table'[Rank] ) <= MAX ( 'TOPN'[TOP N] ),
    CALCULATE (
        SUM ( DATA[SALES] ),
        FILTER ( DATA, DATA[rank] = MAX ( 'Table'[Rank] ) )
    ),
    IF (
        MAX ( 'Table'[Rank] ) = 0,
        CALCULATE ( SUM ( DATA[SALES] ), ALL ( DATA ) )
            - CALCULATE (
                SUM ( DATA[SALES] ),
                FILTER (
                    DATA,
                    DATA[rank] >= MIN ( 'TOPN'[TOP N] )
                        && DATA[rank] <= MAX ( 'TOPN'[TOP N] )
                )
            )
    )
)

        3)When select values in slicer, the result shows:

        See my attached pbix file.

         

        Best Regards,

        Giotto