I need to add three rows together that are currently in the same column and within multiple months.
I need a row called 'Variance' to add together
MFG COST |
LESS ABSORBED - FGM |
LESS ABSORBED - FGB |
for each month. Sum for Actual, Budget and Last Year.
Thanks!
Solved! Go to Solution.
Here's how you can achieve this.
Start by transposing your data. Your Date and FSParent field will be your 'key fields' and your SUM fields will be your data fields.
Once you have performed your transformation, filter your data to only the rows needed. This will give you 9 lines for each date, 3 lines for each metric within that.
Then, use the summerize tool, to calculate your variance. This can be achieved by using the 'Group_By' on your date field and the 'Name' field (which represents the metric name) and summing the value field, I will rename this 'Variance'.
Next I will create a field which gives the name as it will appear in our overall list. Lets again give this as 'Variance'. I will name this column 'FSParent'.
My final step is to cross-tab this datasource so it is inline with the original data. This can be achieved by 'grouping by' our FSParent and Date field. Our column headers will be our 'Name' (the metric). And our value will be the 'Variance' sum column. Be careful, we have to rename our fields after the cross-tab if they contain special characters such as spaces.
Finally I will union this to our original stream.
Example attached.
Ben
@danrh's method is far more efficient! I always pivot when working with multiple columns but in this case it's not necessary.
Ben
hi @britknip,
i am attaching solved workflow. see if that helps.