Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Variable Top N depending on Column Conditions

Hi Everyone,

I want to create a DAX which gives following result :

 

 

So based on the "total" I want to find out "Top 3 brand" for "Category A and B" and "Top 1 Brand" for "Category C".

 

I am able to show top 3 Brands across all categories but facing difficulty with variable Top selection based on Category.

Please help.

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Thanks Hariharan_R. This measure is absolutely correct. But i have one more doubt for the same. 

    How to show multiple Categories in this DAX where i need this condition of variable Top N . For eg,

     

    I have Category D and E as well along with C where this filteration of top N is different as compared to Category A and B.

     

    Thanks.

  • Hariharan_R's avatar
    Hariharan_R
    4 years ago

    Hi,

    If you have more values then try use disconnected table with the TOPN values otherwise try the below one.

     

    Top 3 = 
VAR _N = 
SWITCH(TRUE(), MIN('Table'[Category])="C",2,MIN('Table'[Category])="D",1,MIN('Table'[Category])="E",1,3)
VAR Top3 =
    CALCULATETABLE (
        GENERATE (
            VALUES ('Table'[Category] ),
            TOPN (
                _N,
                CALCULATETABLE ( VALUES ('Table'[Brand] ) ),
                [Sales]
            )
        ),
        ALLSELECTED()
    )
RETURN
    CALCULATE (
        1 * ( NOT ISEMPTY ( 'Table' ) ),
        KEEPFILTERS ( Top3 )
    )

     

    Thanks

    Hari

    Did I answer your question? Then please mark my post as the solution.
    If I helped you, click on the Thumbs Up to give Kudos.

    My Blog :: YouTube Channel :: My Linkedin


6 Replies

  • Hariharan_R's avatar
    Hariharan_R
    Solution Sage
    4 years ago

    Hi,

    You can use below measure.

     

    Top 3 = 
VAR _N = IF(MIN('Table'[Category])="C",2,3)
VAR Top3 =
    CALCULATETABLE (
        GENERATE (
            VALUES ('Table'[Category] ),
            TOPN (
                _N,
                CALCULATETABLE ( VALUES ('Table'[Brand] ) ),
                [Sales]
            )
        ),
        ALLSELECTED()
    )
RETURN
    CALCULATE (
        1 * ( NOT ISEMPTY ( 'Table' ) ),
        KEEPFILTERS ( Top3 )
    )

     

    Sample Data

     

    Thanks

    Hari

     

     

    Did I answer your question? Then please mark my post as the solution.
    If I helped you, click on the Thumbs Up to give Kudos.

    My Blog :: YouTube Channel :: My Linkedin


    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks Hariharan_R. This measure is absolutely correct. But i have one more doubt for the same. 

      How to show multiple Categories in this DAX where i need this condition of variable Top N . For eg,

       

      I have Category D and E as well along with C where this filteration of top N is different as compared to Category A and B.

       

      Thanks.

      • Hariharan_R's avatar
        Hariharan_R
        Solution Sage
        4 years ago

        Hi,

        If you have more values then try use disconnected table with the TOPN values otherwise try the below one.

         

        Top 3 = 
VAR _N = 
SWITCH(TRUE(), MIN('Table'[Category])="C",2,MIN('Table'[Category])="D",1,MIN('Table'[Category])="E",1,3)
VAR Top3 =
    CALCULATETABLE (
        GENERATE (
            VALUES ('Table'[Category] ),
            TOPN (
                _N,
                CALCULATETABLE ( VALUES ('Table'[Brand] ) ),
                [Sales]
            )
        ),
        ALLSELECTED()
    )
RETURN
    CALCULATE (
        1 * ( NOT ISEMPTY ( 'Table' ) ),
        KEEPFILTERS ( Top3 )
    )

         

        Thanks

        Hari

        Did I answer your question? Then please mark my post as the solution.
        If I helped you, click on the Thumbs Up to give Kudos.

        My Blog :: YouTube Channel :: My Linkedin