Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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.
User | Count |
---|---|
99 | |
87 | |
80 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |