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
ampel
New Member

Bar chart based on two tables linked to a third table

Hello, 

 

I searched through some of the older posts and I couldn't find something relevant to what I am after, so I apologise in advance if this is a duplicate post.

I am working on a dashboard for a production company and I have 3 data tables:

 

A) PRODUCT MASTER

It contains all the unique product IDs and descriptions

Capture.JPG

 

B) DISTRIBUTION COSTS

This is effectively a sales report with associated distribution costs. Every material can appear multiple times dependind on the number of customers who purchased it. The main fields are:

MATERIAL - CUSTOMER - VOLUME - SALES VALUE - PRODUCTION COST - EBIT 

 

C) PRODUCTION HOURS

This contains production information for each material. A material can appear multiple times here if it belongs to more than one production lines. The main fields are:

MATERIAL - PRODUCTION LINE - PRODUCTION MACHINE HOURS

 

Both B and C tables are linked to table A using the MATERIAL field (1-to-many).

 

I am trying to create a bar chart visualition where I can see the [table B] SALES VALUE, [table B] PRODUCTION COST, [table B] EBIT by [table C] Production Line, but Power BI doesn't understand the conection between the two tables and it only returns the totals across all products:

 

Capture.JPG

 

Is there a way to work around this? Even if it means that some costs may appear in more than one lines (given that a product can belong to more than one production lines).

 

Thanks in advance

 

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

@ampel,

 

Based on the above, you may set cross filter direction to 'Both' in the Edit relationship dialog.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@ampel,

 

Based on the above, you may set cross filter direction to 'Both' in the Edit relationship dialog.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I can't believe it was that easy! Thanks for the tip, still trying to figure out Power BI 🙂

 

I ended up creating new columns using SUMX(FILTER), but your suggestion works fine too.

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.