Need help with DAX code to show top 10 categories while labelling the other categories as "Others"

[KongZY]

Hi, I am looking to write a DAX measure to find the vendors with the top 5 sales, and to label the other vendors as "Others" as I want to avoid showing too many different vendor names when I build the visual. This is my thought process:

1. Sum up the sales for each vendor
2. Rank the vendors according to their total sales
3. Label the vendors who rank 6 or higher as "Others"

With this thought process, I write the following DAX expression but it did not work:

VendorRanked = IF(

    RANKX(ALL('PurchaseOrders'[Vendor Name]), SUM('PurchaseOrders'[Value])) <= 5,

    VALUES('PurchaseOrders'[Vendor Name]),

    "Others"

)

Not sure what I am doing wrong here, can I have some advice?

My base PurchaseOrder table looks something like this:

Vendor Name	Order No.	Value
Vendor 1	1	10
Vendor 1	2	15
Vendor 2	3	15
Vendor 2	4	15
Vendor 3	5	25
Vendor 4	6	5
Vendor 5	7	30
Vendor 6	8	30
Vendor 7	9	10

And I am looking to get something like this:

Output Column/Measure	Total Value
Vendor 1	25
Vendor 2	30
Vendor 3	25
Vendor 5	30
Vendor 6	30
Others	15

Appreciate if you can provide me with some advice or guidance.

Edit: Link to sample file - https://we.tl/t-NCWML5aA4X 

[ERD]

Hi @KongZY ,

To achieve the result you need to:

1. Create a separate table with all vendors + 'Others'. One of the ways is to do it in Power Query

let
    Source = t,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Vendor Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    Custom1 = Table.InsertRows(#"Removed Duplicates", 0, {[Vendor Name="Others"]})
in
    Custom1

2. Create measures:

total value = SUM(t[Value])

vendor_rank = RANKX ( ALL ( vendors[Vendor Name] ), [total value],, DESC )

custom_value = 
VAR others = FILTER ( ALL ( vendors[Vendor Name] ), [vendor_rank] > 5 )
VAR others_value = SUMX ( others, [total value] )
RETURN
    IF (
        [vendor_rank] <= 5,
        [total value],
        IF ( SELECTEDVALUE ( vendors[Vendor Name] ) = "Others", others_value )
    )

[mh2587]

VendorRanked = // Try this one might help you
VAR VendorSales = 
    SUMMARIZE(
        'PurchaseOrders',
        'PurchaseOrders'[Vendor Name],
        "TotalSales", SUM('PurchaseOrders'[Value])
    )
RETURN
    IF(
        RANKX(VendorSales, [TotalSales], , DESC) <= 5,
        'PurchaseOrders'[Vendor Name],
        "Others"
    )

[KongZY]

Hi Muhammad, thank you for your response. Can I check with you is your solution meant to be a measure or a column?

I have the following issue when I run it as a measure:

And this issue when I run it as a calculated column:

[mh2587]

//Try this one
VendorRanked = 
VAR VendorSales = 
    SUMMARIZE(
        'PurchaseOrders',
        'PurchaseOrders'[Vendor Name],
        "TotalSales", SUM('PurchaseOrders'[Value])
    )
VAR CurrentVendor = VALUES('PurchaseOrders'[Vendor Name])
RETURN
    IF(
        RANKX(VendorSales, [TotalSales], , DESC) <= 5,
        CurrentVendor,
        "Others"
    )

[KongZY]

Hi Muhammad, thank you for your help. I played around a bit and managed to get it to work using

FIRSTNONBLANK('PurchaseOrders'[Vendor Name], [Vendor Name])

inside the RETURN function. I suspect is because the measure would take a column as a return value.