cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NiMa
Frequent Visitor

Variable Top N depending on Column Conditions

Hi Everyone,

I want to create a DAX which gives following result :

 

NiMa_0-1655300179742.png

 

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.

 

1 ACCEPTED SOLUTION

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


View solution in original post

6 REPLIES 6
CNENFRNL
Super User
Super User

Record_2022_06_15_16_08_52_154.gif


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

Hariharan_R
Solution Sage
Solution Sage

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

Hariharan_R_1-1655301607040.png

 

Hariharan_R_0-1655301585856.png

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


NiMa
Frequent Visitor

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,

 

NiMa_0-1655302444057.png

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.

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


your dax function is working fine but i have the categories 6(a,b,c,d,e,f) for each category top 5 brands i want baced on total sales

Hi @jyothi12 

You can remove _N with 5 like below.

 

CALCULATETABLE (
        GENERATE (
            VALUES ('Table'[Category] ),
            TOPN (
                5,
                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


Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors