DAX

[Ar_Sh]

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.

[Jihwan_Kim · Accepted solution]

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:]
    )

[Shubham_rai955 · Accepted solution]

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 - TotalTopN

Label 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 value

Matrix total will show full sales, Top N will show individually, and Others will neatly group the rest.

[Jihwan_Kim · Accepted solution]

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:]
    )