Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I want to show top 20 customers datails & the other customers one row as attached
Solved! Go to Solution.
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
In my sample, I described top 5 but I hope you can make it to show top 20 in your sample.
Please check the below picture and the attached pbix file.
WINDOW function (DAX) - DAX | Microsoft Learn
Top 5 and others: =
VAR _topN = 5
VAR _topNlist =
WINDOW (
1,
ABS,
_topN,
ABS,
ALL ( customer[customer] ),
ORDERBY ( CALCULATE ( SUM ( sales[sales] ) ), DESC )
)
VAR _topNsales =
CALCULATE ( SUM ( sales[sales] ), _topNlist )
VAR _topNsaleskeepfilter =
CALCULATE ( SUM ( sales[sales] ), KEEPFILTERS ( _topNlist ) )
VAR _allsales =
CALCULATE ( SUM ( sales[sales] ), REMOVEFILTERS ( customer[customer] ) )
VAR _othersales = _allsales - _topNsales
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE ( category[category] ) = "others", IF ( NOT HASONEVALUE ( customer[customer] ), _othersales ),
SELECTEDVALUE ( category[category] ) = "top_N", _topNsaleskeepfilter,
_allsales
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
In my sample, I described top 5 but I hope you can make it to show top 20 in your sample.
Please check the below picture and the attached pbix file.
WINDOW function (DAX) - DAX | Microsoft Learn
Top 5 and others: =
VAR _topN = 5
VAR _topNlist =
WINDOW (
1,
ABS,
_topN,
ABS,
ALL ( customer[customer] ),
ORDERBY ( CALCULATE ( SUM ( sales[sales] ) ), DESC )
)
VAR _topNsales =
CALCULATE ( SUM ( sales[sales] ), _topNlist )
VAR _topNsaleskeepfilter =
CALCULATE ( SUM ( sales[sales] ), KEEPFILTERS ( _topNlist ) )
VAR _allsales =
CALCULATE ( SUM ( sales[sales] ), REMOVEFILTERS ( customer[customer] ) )
VAR _othersales = _allsales - _topNsales
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE ( category[category] ) = "others", IF ( NOT HASONEVALUE ( customer[customer] ), _othersales ),
SELECTEDVALUE ( category[category] ) = "top_N", _topNsaleskeepfilter,
_allsales
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
87 | |
85 | |
74 | |
69 | |
65 |
User | Count |
---|---|
214 | |
124 | |
117 | |
82 | |
76 |