March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
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.
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
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 still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
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.
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
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 @Anonymous
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
98 | |
85 | |
69 | |
61 |
User | Count |
---|---|
138 | |
120 | |
109 | |
99 | |
97 |