Calculation Line

[AlteryxLearner23]

Hi,

I have the attached workflow with a sum calculation on the bottom.

I'm trying to enhance this workflow so there is a sum for every two lines.

At the moment we have this

Line 1 Expense         200
Line 1 Payments       -100
Line 2 Expense         300
Line 2 Payments       -50

Calculation                350

I want to change it to look like this

Line 1 Expense         200
Line 1 Payments       -100
Calculation                100
Line 2 Expense         300

Line 2 Payments        -50
Calculation                 250

Every set of data has one line for 'Expense' and one for 'Payments'.

Hope this makes sense.

[ed_hayter]

I went with a summarize tool to group by the ID to create the subtotal - then replicated the column structure so that we can union (stack) the sub-totals back into the original table. Part of that was creating a Forecast Type column for the Sub-totals all with the value =  "sub-totals" so that they can be filtered out when we want to.

[FrederikE]

Hey @AlteryxLearner23,

This Workflow should do that.

Edit: This is massivly overcomplicated, better take @ed_hayter's approach :D 

[KimLamNg]

One way to do this with Crosstab and Transpose.

[AlteryxLearner23]

@FrederikE Thanks for the quick response!

Are you able to add the values and not subtract the values?

[FrederikE]

Hey @AlteryxLearner23,

Yeah, that part is easy at my flow. You can just change the sign in the Multi-Row tool. 

The whole reason that I made it that complicated was that I thought you'd need to substract it :D

[binuacs]

@AlteryxLearner23 Another approach with batch macro

[AlteryxLearner23]

Hi @KimLamNg 

I changed some names in the "Forecast Type" column (attached) and I can't get your workflow to work.

Can you help?

Thanks!

[KimLamNg]

@AlteryxLearner23 You just need to update the the formula tool that does the sum because the field names changed now. Attached is the updated workflow for your reference.