Calculated Column from Two Distinct Tables

[farhandsome]

Hello all,

I am new to Power BI and working on some complex data sources. I am having an issue creating a calculated column associated to some of my data in order to track some production efficiency. I will try my best to describe the context below:

A product we assemble onsite is broken up into various stations that each build a component of the overall assembly. Production associates at each station utilize a PowerApp in order to self-report their build times for their respectibe subassembly. Each build they complete is associated with a unique ID that corresponds to each overall assembly. 

Here is the overall relationship/flow:

'Work Station' [Actual Build Time] -->'Work Station' [ID] --> 'Master ID' [ID] --> 'Master Data List' [ID] --> 'Master Data List' [Material #] --> 'Master Material List [Material #] --> 'Build Time List' [Material #] --> 'Build Time List' [Est. Build Time] (filtered by Work Station)

As you can see, it's a pretty long-winded relationship between the actual build time and the estimated build time. I needed to make a calculated column to measure the efficiency (percentage of estimated / actual). As you can see, the tables do not have the same size so I cannot append them.

What is the easist way to make this measure possible? I have limited knowledge of using DAX in order to filter, look up matching values, etc. and the research on these functions has been difficult to sort through. I'd love if someone could help me get started. 

Let me know if this needs more detail.

Thanks,

F

[v-frfei-msft]

Hi @farhandsome ,

Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[kentyler]

Looks like the key is going to lie in your data model. Its going to be complex to try and walk thru that in messages. Would you like to do a screen share and I'll take a look at what you have and make some suggestions ?