Bar chart based on two tables linked to a third table

[ampel]

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

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:

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

[v-chuncz-msft · Accepted solution]

@ampel,

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

[ampel]

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.