Forum Discussion
TOP N filter within Line and Stacked Column Chart can't filter both lines and columns
Hi Anonymous,
Please check the steps as below.
1 Create a calcualted column.
Column = IF(Table1[Vendor] IN TOPN(4,VALUES(Table1[Vendor]),CALCULATE(SUM(Table1[Order Quantity]),ALLEXCEPT(Table1,Table1[Vendor]))),Table1[Vendor],"OTHERS")
2. Create the measures as below.
B = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="B")) return IF(ISBLANK(sumt),0,sumt)
D = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="D")) return IF(ISBLANK(sumt),0,sumt)
E = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="E")) return IF(ISBLANK(sumt),0,sumt)
F = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="F")) return IF(ISBLANK(sumt),0,sumt)
Others = var sumt = CALCULATE(SUM(Table1[TAT]),FILTER(Table1,Table1[Column]="OTHERS")) return IF(ISBLANK(sumt),0,sumt)
For more details, please check the pbix as attached.
Regards,
Frank
Hi Frank,
Here are my sample data:
Note: The original data doesn't have column B. I created column B (Big Supplier Names by Net Order Quantity) because I couldn't find a way to have my chart displayed TOP 4 and Others. Again, this is time consuming and I might have to repeat this step everytime I receive a new dataset to see who are my Top 4 Suppliers and set the rest of the suppliers to be "Others", which then makes Top 4 and Others.
Here is my expected result:
I have this slicer which was created by "Type" column.
I'd like my graph to only show TOP 4 and OTHERS with their corresponding TAT lines whenever a selection (either single or multiple option(s)) is made in the "Select Type" slicer.
*Column values = Order Quantity
*Line values = TAT
And is it possible to get the graph worked out the way I want without manually setting certain suppliers as Big Suppliers and the rest as Others in Excel?
Thank you so much!!!
Hi everyone,
I have the following Line and Stacked Column Charts (they have the same elements, only filtered by different values) :
Chart 1:
Column values = Order Quantity of each supplier
Line values = Average TAT of each supplier
Chart 2:
Column values = Unit Price of each supplier
Line values = Average Net Order Cost of each supplier
* Since the 2 graphs are supposed to work and function the same. I will just describe what data I've got for one chart.
*Data Source = Excel
Here are some other data I used for my chart:
Shared axis: POReceivedDate (Data TAT table)
Note: Sorted by Year-Quarter-Month Date Hierarchy
Column series: Big Supplier Names (Data TAT table)
Note: In Excel, I manually put 12 biggest supplier names (taken from Vendor column which has all supplier names) based on Order Quantity and Net Order Cost in a column named Big Supplier Names and the rest of the suppliers as "Others".
Column values: Order Quantity (Data TAT table)
Line values: Average TAT of those 12 mentioned suppliers ; Average TAT of Others
Note: I use the following measure to calculate Average TAT of each supplier.
Average TAT - Supplier A =
CALCULATE(
AVERAGE('Data TAT'[TATclosed]),
'Data TAT'[Big Supplier Names]
IN { "Supplier A" }
)
I also have a slicer that looks something like this:
What it does is that the graph above will respond accordingly to the type(s) being selected in this slicer.
I'd like for each/all type(s) selected, the graph only shows Top 4 Suppliers and Others by Quantity Order.
So, in the Visualisations pane -> FILTERS -> Visual level filters -> Big Supplier Names -> Filter TOP 5 by Order Quantity
Does anyone know how I can filter my Top 5 Suppliers (or Top 4 suppliers and Others) by Order Quantity and the graph only shows TAT lines corresponding to the Top 5?
Thank you very much!