Forum Discussion
DAX
Trying to build a customer performance view where a slicer controls the Top N value. The matrix should display the Top N customers by sales and then show a single Others row that groups all remaining customers. Total Sales and a What If parameter for selecting N already exist. Guidance is needed on writing DAX that returns the Top N customers correctly, calculates Others as the remaining sales, and keeps the matrix total showing the full combined amount without breaking the visual layout.
Hi,
Please check the below picture and the attached pbix file.
I tried to create a sample pbix file like below.Sales: = SUM(sales[sales])Top N sales: = VAR _topN = [prm_top_N Value] VAR _topNtable = WINDOW ( 1, ABS, _topN, ABS, ALL ( Customer[customer] ), ORDERBY ( [Sales:], DESC ) ) VAR _topNsales = CALCULATE ( [Sales:], KEEPFILTERS ( _topNtable ) ) RETURN SWITCH ( TRUE (), SELECTEDVALUE ( Customer[customer] ) = "Others", CALCULATE ( [Sales:], REMOVEFILTERS ( Customer[customer] ) ) - CALCULATE ( [Sales:], _topNtable ), HASONEVALUE ( Customer[customer] ), _topNsales, [Sales:] )You can handle Top N plus an Others row using two measures and a display logic measure.
Top N Customers
TopN Customers = VAR N = SELECTEDVALUE('TopN Parameter'[TopN Value], 5) RETURN IF( RANKX( ALL('Customer'[Customer Name]), [Total Sales], , DESC ) <= N, [Total Sales] )Others Sales
Others Sales = VAR N = SELECTEDVALUE('TopN Parameter'[TopN Value], 5) VAR TopNTable = TOPN(N, ALL('Customer'[Customer Name]), [Total Sales], DESC) VAR TotalAll = CALCULATE([Total Sales], ALL('Customer')) VAR TotalTopN = CALCULATE([Total Sales], TopNTable) RETURN TotalAll - TotalTopNLabel Measure
Customer Label = VAR N = SELECTEDVALUE('TopN Parameter'[TopN Value], 5) VAR IsTopN = RANKX( ALL('Customer'[Customer Name]), [Total Sales], , DESC ) <= N RETURN IF(IsTopN, SELECTEDVALUE('Customer'[Customer Name]), "Others")Use Customer Label in the matrix rows
Use TopN Customers as the value
Add Others Sales as a second valueMatrix total will show full sales, Top N will show individually, and Others will neatly group the rest.
2 Replies
Hi,
Please check the below picture and the attached pbix file.
I tried to create a sample pbix file like below.Sales: = SUM(sales[sales])Top N sales: = VAR _topN = [prm_top_N Value] VAR _topNtable = WINDOW ( 1, ABS, _topN, ABS, ALL ( Customer[customer] ), ORDERBY ( [Sales:], DESC ) ) VAR _topNsales = CALCULATE ( [Sales:], KEEPFILTERS ( _topNtable ) ) RETURN SWITCH ( TRUE (), SELECTEDVALUE ( Customer[customer] ) = "Others", CALCULATE ( [Sales:], REMOVEFILTERS ( Customer[customer] ) ) - CALCULATE ( [Sales:], _topNtable ), HASONEVALUE ( Customer[customer] ), _topNsales, [Sales:] )You can handle Top N plus an Others row using two measures and a display logic measure.
Top N Customers
TopN Customers = VAR N = SELECTEDVALUE('TopN Parameter'[TopN Value], 5) RETURN IF( RANKX( ALL('Customer'[Customer Name]), [Total Sales], , DESC ) <= N, [Total Sales] )Others Sales
Others Sales = VAR N = SELECTEDVALUE('TopN Parameter'[TopN Value], 5) VAR TopNTable = TOPN(N, ALL('Customer'[Customer Name]), [Total Sales], DESC) VAR TotalAll = CALCULATE([Total Sales], ALL('Customer')) VAR TotalTopN = CALCULATE([Total Sales], TopNTable) RETURN TotalAll - TotalTopNLabel Measure
Customer Label = VAR N = SELECTEDVALUE('TopN Parameter'[TopN Value], 5) VAR IsTopN = RANKX( ALL('Customer'[Customer Name]), [Total Sales], , DESC ) <= N RETURN IF(IsTopN, SELECTEDVALUE('Customer'[Customer Name]), "Others")Use Customer Label in the matrix rows
Use TopN Customers as the value
Add Others Sales as a second valueMatrix total will show full sales, Top N will show individually, and Others will neatly group the rest.