Incorrect division measure after filtering with Inputs from two different tables

[MohamedKamel]

Hello Everyone,

I have an issue with a division measure called 'Breakdown %' that calculates based on column 'Breakdown Time(hours)' from Table1 and column 'Loading Time (hours)' from Table2.

So I created a Compound Key in Table1 with the following equation

Compound Key = Table1[Date]&"-"&Table1[Area]&"-"&Table1[Line]&"-"&Table1[Machine]

and another Compound Key created in Table2 with the following equation

Compound Key = Table2[Date]&"-"&Table2[Area]&"-"&Table2[Line]&"-"&Table2[Machine]

I created Table3 with the following equation and connected Table3 to both Table1 and Table2 as one-to-many through the Compound key as shown in the below photo.

Table3 = DISTINCT(UNION(SUMMARIZE(Table1,Table1[Date],Table1[Area],Table1[Machine],Table1[line],Table1[Compound Key]),SUMMARIZE(Table2,Table2[Date],Table2[Area],Table2[Machine],Table2[line],Table2[Compound Key])))

Please note that the same mapping and texts are used for the four columns 'Date', 'Area', 'Line', and 'Machine' in both Table1 and Table2 where each Date contains specific Areas and each Area contains specific Lines and each Line contains specific Machines where these four columns are the ones used to create the compound keys mentioned above.

The issue here is that the division measure 'Breakdown %' only gives correct calculation when no filter is selected or when selecting a specific text in the 'Area' column however when selecting any text in the 'Line' or 'Machine' columns the measure does not calculate the correct division for the specific selected text for these two columns.

I need support to have the correct measure calculation when selecting a specific text in the 'Line' column or in the 'Machine' column.

[MohamedKamel]

@amitchandak  you were right I didn't use the data from table 3 for all visuals. Thank you for your support.

[amitchandak · Accepted solution]

@MohamedKamel , Share the measure formula

a calculation like should work

Divide(Sum(Table1[Breakdown Time(hours)) , Sum(Table2[Loading Time (hours)]) )

Common column from Table3 should be used in visual

[MohamedKamel]

@amitchandak  this is the measure formula currently used 

% Breakdown = DIVIDE(SUM('Table1'[Breakdown Time(hours)]), SUM('Table2'[Loading Time (hours)]))

and as mentioned before it already works when no filter is selected or when a filter is selected from the 'Area' column but not when a filter is selected from the 'Line' column or the 'Machine' column.