ADD MULTIPLE ROWS IN SAME COLUMN

[britknip]

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!

[danrh · Accepted solution]

I'm not 100% positive on your requirements, but something like this?

This adds up the three rows by month, then adds a row at the bottom of each month called "Variance".

Hope it helps, reach out if this is off-base!

[BenMoss]

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

[BenMoss]

@danrh's method is far more efficient! I always pivot when working with multiple columns but in this case it's not necessary. 

Ben

[bharti_dalal]

hi @britknip,

i am attaching solved workflow. see if that helps.

[britknip]

I was able to get both @danrh and @BenMoss answers to work so thank you both!.

Also, these are great for summing but what if I wanted to subtract a field. For example:

Sales - Price Allowances = Net sales

[danrh]

For subtracting, I would add in a Multi-Field Formula tool to change the "Sum_" fields to negative for Price Allowance, then use the same approach to add them up.

I didn't find Sales records, so I made some assumptions: