cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Create a table using specific columns of other tables

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

 

 

 

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: Create a table using specific columns of other tables

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Create a table using specific columns of other tables

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

 

 

Chava1881_0-1595001257878.png

 

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

Chava1881_1-1595002024225.png

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors