Calculated Column to extract Top 5 Supplier Names based on a fixed-value-table

[Anonymous]

I'm trying to calculateTop 5 Vendors by Total Order Quantity in a line-and-stacked-column chart but it has to response to a slicer

Here is my data:

1. Big Suppliers Data table:

• Vendor: 39 vendor names (and this column links with the "Vendor" column in the other table described below)

• Big Supplier: only 7 names appear here while the rest are grouped to "Others"

2. Data TAT table (transaction data):

• Vendor

• Order Quantity (1 for every transaction)

• TATClosed

• ReceivedDate

• Business Type (DET or LRU option)

One vendor can have 2 types of businesses LRU & DET while some might only have 1. As you can see in the 1st picture above, there're only 7 names out of many vendor names appear while the rest are grouped to "Others". That's why If I used "Big Supplier" column as my column series and choose DET or LRU from the slicer, I'd always get all 7 names which is Top 7 and not Top 5 in each business.

How can I use those set-up names to create a Column that can be used to show my Top 5 vendors by Total Order Quantity while making sure it is responsive to a slicer created by "Business Type" column? Because my data already has "Others" and any search results in this forum I was able to find all computed those who are not in TOPN to be "others", which is not what I need or what is applicable to my problem.

Does anyone know how i can attach my sample file here? Thanks.

[AlB]

Hi @Anonymous

Could you share the pbix file? It'd be easier to understand your explanation.

So you say you're creating a column as below. Can you explain what the ALLSELECTED is for here?

 RANK BY ORDER QUANTITY=
 IF (
    RANKX (
        ALLSELECTED ( 'Data TAT'[Vendor] ),
        CALCULATE (
            [SUM ORDER QUANTITY],
            ALLEXCEPT ( 'Data TAT', 'Data TAT'[Vendor], 'Data TAT'[LRU-DET] )
        )
    )
        <= 4,
    'Data TAT'[Vendor],
    "OTHERS"
)

[Anonymous]

Hi @AlB I have fixed my description as it was a bit confusing. I'm trying to attach my same PBI file but couldn't find a way. Do you know how? (Apologise to ask such a question but I'm new to this platform). Thanks!

[AlB]

@Anonymous

Sure, you cannot attach it. You gotta post the URL to the file on Dropbox, Onedrive etc.

OR you can quickly upload the file to a website like this (no sign-in required) and post the URL here.