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!

Work Ex1.xlsx

Preparation

Accepted answers

danrh

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!

solution.yxmd

All comments

danrh

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!

solution.yxmd

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

Example.yxzp

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. Screenshot (178).png

sum_colums.yxzp

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167