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 th...
Need Help
Tutorial Requests
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"
    )
    • KongZY's avatar
      KongZY
      New Member
      2 years ago

      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.