Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am looking for some help as I am struggling to get the DAX correct -
I am needing to make a visualisation which simply shows 2 measures:
Top 10 Sales by Customer
All Other Customers
I am struggling the top N concept to show simply the grand total of the top 10 highest sales by customer, after this i could then subtract from all sales.
Any help with the DAX appreciated 🙂
Solved! Go to Solution.
Okay, so, I implemented a solution. Hope this helps.
First you will need an auxiliar table in order to show Top 10 and Others in a visual, the DAX for your table looks like this:
Top Customers =
{ "Top 10 Customers", "Others" }
Then you will create a Ranking measure like the one below:
Ranking =
RANKX(
ALL( Customer[Name] ),
[Sales]
)
Then you will need your final measure which is the one that splits the ranking:
Top 10 & Others =
VAR _Rank =
ADDCOLUMNS(
ALL( Customer[Name] ),
"@Sales", [Sales],
"@Rank", [Ranking]
)
VAR _Top10 =
SUMX(
FILTER(
_Rank,
[@Rank] <= 10
),
[@Sales]
)
VAR _Others =
SUMX(
FILTER(
_Rank,
[@Rank] > 10
),
[@Sales]
)
VAR _Result =
SWITCH(
SELECTEDVALUE( 'Top Customers'[Value] ),
"Top 10 Customers", _Top10,
"Others", _Others,
BLANK()
)
RETURN
_Result
Last step, drag what you need into a table, that is "Value" from your newly created table ( you can name it whatever you need, by default will be "Value" ) and your "Top 10 & Others" measure. The result should look like this:
I hope this helps solving your requirement!
Thanks but I am afraid not, what I am looking for is a dynamic measure that returns a table that looks like the below.
Okay, so, I implemented a solution. Hope this helps.
First you will need an auxiliar table in order to show Top 10 and Others in a visual, the DAX for your table looks like this:
Top Customers =
{ "Top 10 Customers", "Others" }
Then you will create a Ranking measure like the one below:
Ranking =
RANKX(
ALL( Customer[Name] ),
[Sales]
)
Then you will need your final measure which is the one that splits the ranking:
Top 10 & Others =
VAR _Rank =
ADDCOLUMNS(
ALL( Customer[Name] ),
"@Sales", [Sales],
"@Rank", [Ranking]
)
VAR _Top10 =
SUMX(
FILTER(
_Rank,
[@Rank] <= 10
),
[@Sales]
)
VAR _Others =
SUMX(
FILTER(
_Rank,
[@Rank] > 10
),
[@Sales]
)
VAR _Result =
SWITCH(
SELECTEDVALUE( 'Top Customers'[Value] ),
"Top 10 Customers", _Top10,
"Others", _Others,
BLANK()
)
RETURN
_Result
Last step, drag what you need into a table, that is "Value" from your newly created table ( you can name it whatever you need, by default will be "Value" ) and your "Top 10 & Others" measure. The result should look like this:
I hope this helps solving your requirement!
Works great! Thank you 🙂
Hi there!
You can refer to this post that shows how to implement a top N
https://radacad.com/top-n-filter-in-power-bi-with-zero-dax-code-visual-level-filter
Let me know if that helps!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
53 | |
46 | |
15 | |
12 |