Create a table using specific columns of other tables

[Chava1881]

Hello,

I am new to PowerBI and I have an assignment that I need help with two things because I don't really know how to tackle this. 

First Issue:

I have three Excel datasets:

1. Product information and details such as description, location, and Forecast qty: Forecast comes in 13 rolling weeks so each week there's a new week added to as ending column and the second column becomes the first one week over week.

2. Product information, also same details on dataset 1 with two additional columns to specify production timestamp and location and current week inventory target, this is rolling, every week, the week number changes and sometimes the target does as well.

3. Product information, location, std cost, On Hand QTY, Open Order QTY and Customer Orders QTY

I need to get just one table out of this one, this table must be based on dataset 2, must have std cost, On hand, OpenOrder QTY and Cust Order QTY along with Forecast of table number 1.

So I created a key to linked them all, I concatenated the Product ID and the Location ID so that I could get an unique identifier, and setup relationships then I created a new table using this concatenation from dataset2 and tried to bring using RELATED but didn't work, even though I had relationships setup, PBI only had found data on the table I used as base which was dataset2. 

I tried to do a merge didn't work, merged all columns but only filled out data where fits, so I didn't get a table that had a single row with all data I needed, for example in std cost I got Null in row where product A according to dataset2 is but then I got the key alone by itself only with std cost. I need them all in the same row, append didn't work either, didn't let me use more than one table and got nowhere to go from this point onwards.  Any guidance here I would really appreciate it.

The second issue I have would be that this table I am aiming to create would have calcuations that need data from the columns just added, and the outputof one calculated field would be input for another one and so on so forth and I don't know if that's even possible. For example, I need to calculate cycle stock (homemade formula to serve situation), the formula is easy, it is just the average of forecast multiply by 2.  and Weeks of supply that is Target/forecast average

Then the Cycle stock is used to calculate whether we are on or off with inventory and forecast, so would get formula that looks like this: (On hand - cust orders - cycle cycle stock) / Weeks of supply

if some one can help me understand whether this is possible I would really appreciate it

[Greg_Deckler]

@Chava1881 - Sample data would really, really help to visualize this. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Chava1881]

Thanks for the reply.  I am adding more data now.

Screenshot below shows the tables I have and relationships, I could actually create the table in the report section but I am having an issue now with the second part which are calculated colums, I have created a measure to have an average of the forecast, because if I create a column it doesn't work,  that average of forecast is used to calculate Cycle, Cycle time is needed to create like three to four more columns and I am getting this error "A circular dependency was detected" along with the calculations that use the measure created, I am not a computer science guy so I don't understand what's going on at a system level to correct it. I've tried duplicating the FCST column to create a second measure, I created a different measure using the average times 1, didn't work either.

Formula example:

1.Weeks of Supply (WoS) = Buffer QTY/FCST Average

2.Cycle Stock: FCST Average *2

3.Total Inventory QTY: Cycle Stock + Buffer QTY

4.Net Inventory Over/Under (to understand if we are off/on buffer): On Hand QTY-(Backlog+Total Inventory QTY)

5.Adherence (to understand how many times buffer strategy covers inventory needs or is missing to cover): (on hand-Backlog-cycle stock)/Buffer QTY

6.Hit/Miss (just a simple calculation to say 1 when on hand covers and 0 when doesn't):  if(On Hand>=(Backlog+CycleStock+buffer QTY),1,0)

7. Total Inventory value: Total Inventory QTY* Std Cost

8. Net Inventory Over/Under value: Net Inventory Over/Under qty * Std Cost

Screenshot of Circular dependency. I just have been able to create the first 2 formulas listed above and then get the circular dependency error.