3 data tables - circular relationship

[coffjoh2004]

I am creating a model with 3 data tables. The end goal is to create a tree/hierarchy that calculates total cost per number of work orders, by outage code, then by cost center, then by gang number. Total costs are in the all outages table, and all of the work orders are in the WORKORDER table. The merged table is to map the crew numbers to the gang numbers with the cost centers. I need the following relationships to all be active: Outage codes from 'all outages' to 'WORKORDER', crew numbers from 'WORKORDER' to "merged table", and cost centers from "All outages"
to "merged table". But this creates a circular relationship and they can't all be active. But if i get rid of one, either the total costs dont flow through to the cost centers, or the number of work orders dont flow through to the cost centers. Any help would be appreciated

[vanessafvg]

its hard to give advice with limited information, providing sample data is usually the best way to get your answer seen to.

however from what you have shared it makes more sense to me to create 3 new tables 

a cost centre table with a unique list of cost centres

a outages code table with a unique list of outages code

crew numbers  table with a unique list of crew numbers  

then use these intermediate tables to link the tables together via these tables.

so workorder and outages will link to outage codes

work order and merged will link to crew numbers 

cost cent4res will link to outages and merged

these will be one to many relationships from the intermediate tables

the bridge tables must have unique values 

however not sure if this will resolved all the issues, the way you have modelled your data needs to adjust in order to meet your requirements

providing sample data is the easiest way to figure it out.

[coffjoh2004]

The relationships are working great, thank you so much. 

My issue now is that i'm having to work backwards via the crews to get to the cost center, and the work order data is not flowing through correctly. Example for one specific outage code:

Cost center 1622 has 2 crews associated with it - crew 1 has 108 work orders and crew 2 has 2 work orders ( this is strictly from the work order table). So the total for that cost center (in one specific outage) should be 110 work orders. But in the decomposition tree, it's carrying over the total number of work order for the entire outage code, something in the thousands, because of the outage code connection between the outages table and the work order table. How would I be able to combine all of the totals for each crew into one total for each cost center?

P.S sorry for the vague details, this is a report for my company and i am worried about confidentiality.

[vanessafvg · Accepted solution]

i think your transactional data probably needs adjusting, so work orders, merged tables and outages, how do they all relate to each other?  it might be better to denormalise this data, ie flattten as much as you can into one or 2 transactional tables, its about understanding how these processes fit together.    So does an outage have more than one work order?  in power query it might make sense to on these relationships to merge the tables together.   that way you wont have to work backwards because its in the same table.  Its about understanding if the granularity of the table changes and if it makes sense to do that.  Not sure if this makes sense.   You can always create a few rows of sample data in mockaroo an online free way to make sample data if that is easier.