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

Calculation using data from 2 tables

Hello, 

 

I have 2 tables.  One containing index info for current and prior year.

 

Formula IDIndexJan_PriorFeb_PriorJan_CurrentFeb_Current
267383M3831.251.291.291.28
267383P3830.051.050.041.04

 

The other contains sku info such as usage, conversion, and volume.

 

Formula IDVendor NameVendor NumberPlant NamePlant NumberMaterialFormula NameusageconversionJan_VolumeFeb_Volume
267383TestTest1Test251080020608T033831.1771550005000
267383TestTest1Test25108200016797043834.80713.082350005000
267383TestTest1Test25108200020499043830.6278.1795000

5000

 

 

 

I want to perform a calculation such as below:

 

(((Index M383 Jan_Current * Usage) - (Index M383 Jan_Prior * Usage)) + ((Index P383 Jan_Current * Usage) - (Index P383 Jan_Prior * Usage))) * Jan_Volume

 

How can I pull in both indexes into the sku table so that I can perform the calculation?

 

*My full tables will contain numerous index and sku rows with various formulas being applied based on the formula ID and the formula will be performed for each of the 12 months.

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Calculation using data from 2 tables

Hi @9mikejacobs,

I am afraid you could not achieve your result, due to the Fromula ID in your two tables are all duplicate, I could not figure out which row in the first table can match to the related row in the second table.(For example, the ID of Index M383 is 267383, which row should be calculated in the second table?) Could you please offer more information about your logic of your data if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Calculation using data from 2 tables

Hey Daniel, 

 

I have a total of 3 tables of data.  Volume data, Index data and then the sku data.  I have no issue merging the volume and sku data as they have a unique ID that concatenates the Sku #, Plant # and Vendor #.  My dilemma is the index data.  Ideally I want to maintain a table similar to the below that logs the Index and its values for current and prior year, but in some cases I need to use multiple indexes across one row in the sku data.  

 

  • I have a formula price agreement for each sku
  • I have volume data for each sku
  • I want to calculate the YoY delta in the formula for the index portion of the pricing
    • The full formula for 267383 is (((M383 * usage)+(P383 * usage)) + conversion) * 1000
    • M383 and P383 change each month and I want to calculate the financial impact of the change YoY.  I am not interested in the conversion cost as it is a fixed cost.
  • When a formula only contains one index I have no issue merging that data and performing the YoY calculation.  It's when the formula contains more than one index that I'm getting stumped.  Not sure of the best way to structure the tables to create that link. 

 

 

Formula IDIndexJan_PriorFeb_PriorJan_CurrentFeb_Current
267383M3831.251.291.371.33
267413M413LC1.171.21.231.24
425DefaultPriorMonthMidwestAverage0.980.991.21.25
267383P3830.051.052.053.05

 

One thought I have is to set up the table like below, but that could get a bit messy as I will likely have around 40 or so indexes in the table.  Some formulas may use as many as 4 indexes.

 

Formula IDJan_Prior_M383Jan_Current_M383Jan_Prior_P383Jan_Current_P383
2673831.251.291.052.05

 

Thanks, 

Mike

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors