Reply
Regular Visitor
Posts: 37
Registered: ‎09-20-2018

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

[ Edited ]

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.

Highlighted
AlB Super Contributor
Super Contributor
Posts: 1,168
Registered: ‎11-12-2018

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

Hi @iamtrangdoan

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

 

 

 

Regular Visitor
Posts: 37
Registered: ‎09-20-2018

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

[ Edited ]

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 Super Contributor
Super Contributor
Posts: 1,168
Registered: ‎11-12-2018

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

@iamtrangdoan

 

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.