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

TOP N filter within Line and Stacked Column Chart can't filter both lines and columns

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.

 

12.JPG

 

*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:

8.JPG

 

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

 10.png

 

 

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!

 

 

 

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

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)

Capture.PNG

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi,

I am trying to display line and stack column chart using two different data sources. Productivity row from first table as line values and task type from below table as column values. But it seems like productivity line is showing as total values 1076 as a straight line instead of showing breakdown by daily data as in the first table. Is it possible to make productivity data shows as a breakdown daily data instead of showing total value in line and stack column chart? First table and second table is from different data sources and cannot find relationship between these two tables. Please advise. Thank you in advance. 

PowerBI.PNG

v-frfei-msft
Community Support
Community Support

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)

Capture.PNG

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @Anonymous,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.


Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
v-frfei-msft
Community Support
Community Support

Hi @Anonymous,

 

Kindly share your sample data and excepted result to me.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hi Frank,

 

Here are my sample data:

 

Capture.JPG

 

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:

 

 1.JPG

 

 

I have this slicer which was created by "Type" column.

 

8.JPG

 

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!!!

 

Anonymous
Not applicable

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.

 

12.JPG

 

*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:

8.JPG

 

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

 10.png

 

 

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!

 

 

 

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.