Forum Discussion

Ar_Sh's avatar
Ar_Sh
Advocate II
8 months ago
Solved

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...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    8 months ago

    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's avatar
    Shubham_rai955
    8 months ago

     

    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.

Shubham_rai955's avatar
Shubham_rai955
Super User
8 months ago

 

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.