Forum Discussion

KongZY's avatar
KongZY
New Member
2 years ago

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

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 NameOrder No.Value
Vendor 1110
Vendor 1215
Vendor 2

3

15
Vendor 2415
Vendor 3525
Vendor 465
Vendor 5730
Vendor 6830
Vendor 7910


And I am looking to get something like this:

Output Column/MeasureTotal Value
Vendor 125
Vendor 230
Vendor 325
Vendor 530
Vendor 630
Others15

 

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

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

Need Help
Tutorial Requests

5 Replies

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    2 years ago
    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's avatar
      KongZY
      New Member
      2 years ago

      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's avatar
        mh2587
        Icon for Super User rankSuper User
        2 years ago
        //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"
    )
  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    2 years ago

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