Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have 2 tables. One containing index info for current and prior year.
Formula ID | Index | Jan_Prior | Feb_Prior | Jan_Current | Feb_Current |
267383 | M383 | 1.25 | 1.29 | 1.29 | 1.28 |
267383 | P383 | 0.05 | 1.05 | 0.04 | 1.04 |
The other contains sku info such as usage, conversion, and volume.
Formula ID | Vendor Name | Vendor Number | Plant Name | Plant Number | Material | Formula Name | usage | conversion | Jan_Volume | Feb_Volume |
267383 | Test | Test1 | Test2 | 5108 | 0020608T03 | 383 | 1.177 | 15 | 5000 | 5000 |
267383 | Test | Test1 | Test2 | 5108 | 20001679704 | 383 | 4.807 | 13.0823 | 5000 | 5000 |
267383 | Test | Test1 | Test2 | 5108 | 20002049904 | 383 | 0.627 | 8.179 | 5000 | 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.
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
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.
Formula ID | Index | Jan_Prior | Feb_Prior | Jan_Current | Feb_Current |
267383 | M383 | 1.25 | 1.29 | 1.37 | 1.33 |
267413 | M413LC | 1.17 | 1.2 | 1.23 | 1.24 |
425Default | PriorMonthMidwestAverage | 0.98 | 0.99 | 1.2 | 1.25 |
267383 | P383 | 0.05 | 1.05 | 2.05 | 3.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 ID | Jan_Prior_M383 | Jan_Current_M383 | Jan_Prior_P383 | Jan_Current_P383 |
267383 | 1.25 | 1.29 | 1.05 | 2.05 |
Thanks,
Mike
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |