Calculating ROWs in alteryx

[SahadhKuruniyan]

For one of my project I need to calculate subtotal in ROW. I have spent 3 weeks figuring out a way in alteryx but nothing did not work. Please see below table. I need to calculate rows where there is X.

Account	2022	2021
Interest Expense	4353	1315
Interest Income	4432	3232
Net Interest	xx	xx
Commissions	232	4342
Other income	323	322
Other expense	3232	5344
Net income	xx	xx

I am thinking to have this table in a Horizontal format and do a normal formula tool calculation. Is it possible to convert the table within the alteryx

Account	Interest Expense	Interest Income	Net Interest	Commissions	Other income	Other expense	Net income
2022	4353	4432	xx	232	323	3232	xx
2021	1315	3232	xx	4342	322	5344	xx

[jdminton]

I've included your request in the attached workflow, but the container identified as Preferred will allow you to set up your structure and prevent you from manually needing to create formulas. You will likely need to update the Template input to fit your output desired, but it will save you tons of time in the long run.

[nagakavyasri]

[Vidhatri]

Thank you for your input, it was really helpful. I tried replicating this on my workflow and used 'Formula' tool to get the row totals (i.e., Net Interest = Interest Revenue + Interest Expense)

However, I would like to know how can we get the data back into the original format after calculating the row totals i.e., horizontally? 

I would like to see the output in the below format after the row totals (i.e., Net Interest) are calculated:

Account	Dec22_ytd	Dec22_qtd	Mar23_ytd	Mar22_ytd
Interest Expense	-10	-20	-30	-40
Interest Revenue	30	50	40	60
Net Interest	20	30	10	20

I have also attached the workflow screenshot as well. 

[flying008]

Hi, @Vidhatri 

[Qiu]

@SahadhKuruniyan 
I answered a similar question earlier I think.
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Complete-Table-Transpose/td-p/...

[jdminton]

@Vidhatri I updated the workflow to do this. It is really just another transpose and crosstab after the formula. I've highlighted the new portion in the green container.

The reason I included the RecordID as part of the account was to be able to sort the line items in the required order. Once you flip them back, the sorting can become different. That record ID will allow you to sort in the desired order. You could also use the structure and join method I have in the second workflow.