Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
9mikejacobs
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
v-danhe-msft
Employee
Employee

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.