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 a...
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 )
        )
    )