cancel
Showing results for
Did you mean:
Frequent Visitor

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

1 ACCEPTED SOLUTION
Solution Sage

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

If I helped you, click on the Thumbs Up to give Kudos.

6 REPLIES 6
Super User

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

Thanks

Hari

If I helped you, click on the Thumbs Up to give Kudos.

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,

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.

Solution Sage

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

If I helped you, click on the Thumbs Up to give Kudos.

Frequent Visitor

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

Solution Sage

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

If I helped you, click on the Thumbs Up to give Kudos.

Announcements