12-07-2018 01:28 PM - edited 12-10-2018 09:42 AM
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):
• Order Quantity (1 for every transaction)
• 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.
12-08-2018 03:26 AM
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" )
12-10-2018 08:47 AM - edited 12-10-2018 08:50 AM
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!
12-10-2018 10:23 AM