Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
AlB
Super User
Super User

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

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!

 

 

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.