cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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.

3 REPLIES 3
Super User
Super User

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

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
Not applicable

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

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!

 

 

Super User
Super User

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

@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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 41 members 1,067 guests
Please welcome our newest community members: