Forum Discussion

YellowMountain's avatar
YellowMountain
Regular Visitor
4 years ago
Solved

Top 10/Other

I have created a Power BI table which ranks the top 10 customers by sales.  What now I need to do is take all of the remaining customers and combine them into one customer named "Other" and post it at the bottom of the table (as shown below),but keeping the names in the top 10 and the rest a grouping with "Other",But I can't use calculated column, as it impacts processing.   What is the best way to accomplish this?

 

 

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below, I hope the below can provide some ideas on how to create measures for your data model.

     

     

     

     

    Top N measure: = 
VAR topNnumber = SELECTEDVALUE('topN'[topN] )
VAR topNtable =
    TOPN (
        topNnumber,
        ALL ( Customers[Customer] ),
        CALCULATE ( SUM ( Data[Value] ) ), DESC
    )
VAR showtopN =
    CALCULATE ( SUM ( Data[Value] ), KEEPFILTERS ( topNtable ) )
VAR othersvaluetotal =
    CALCULATE ( SUM ( Data[Value] ), ALL ( Customers[Customer] ) ) - CALCULATE ( SUM ( Data[Value] ), topNtable ) 
RETURN
    IF (
        ISFILTERED ( Customers[Customer] ),
        SWITCH (
            SELECTEDVALUE ( Customers[Customer] ),
            "Others", othersvaluetotal,
            showtopN
        )
    )

     

     

     

     

     

    Ranking measure: = 
VAR topNtable =
    FILTER (
        ADDCOLUMNS ( ALL ( Customers[Customer] ), "@topNvalue", Data[Top N measure:] ),
        [@topNvalue] <> BLANK () && Customers[Customer] <> "Others"
    )
VAR counthowmanyN =
    COUNTROWS ( topNtable )
RETURN
    IF (
        NOT ISBLANK ( Data[Top N measure:] ),
        SWITCH (
            SELECTEDVALUE ( Customers[Customer] ),
            "Others", counthowmanyN + 1,
            RANKX ( ALL ( Customers[Customer] ), CALCULATE( SUM(Data[Value]) ),, DESC )
        )
    )