I have a dataset where I finding very to difficult to calculate rows Example: Fundamountprior year amounttypeABC123145InvestmentABC145156TotalLMN145615InvestmentLMN15923TotalXYZ45656InvestmentXYZ2314Total In About all, I have to find a difference in investment and total (Type column) Fundamountprior year amounttypeABC123145InvestmentABC145156TotalABC-22-11DifferenceLMN145615InvestmentLMN15923TotalLMN1297-8DifferenceXYZ45656InvestmentXYZ2314TotalXYZ43342Difference I am not sure if it will be easy to transpose and do it or it should be done easily in row formula

I want to calculate difference in a dataset

I have a dataset where I finding very to difficult to calculate rows

Example:

Fund	amount	prior year amount	type
ABC	123	145	Investment
ABC	145	156	Total
LMN	1456	15	Investment
LMN	159	23	Total
XYZ	456	56	Investment
XYZ	23	14	Total

In About all, I have to find a difference in investment and total (Type column)

Fund	amount	prior year amount	type
ABC	123	145	Investment
ABC	145	156	Total
ABC	-22	-11	Difference
LMN	1456	15	Investment
LMN	159	23	Total
LMN	1297	-8	Difference
XYZ	456	56	Investment
XYZ	23	14	Total
XYZ	433	42	Difference

I am not sure if it will be easy to transpose and do it or it should be done easily in row formula

Datasets

Developer

Tips and Tricks

Common Use Cases

Best Practices

Transformation

Preparation

Accepted answers

All comments

ShankerV

Hi @Sshasnk

It can be easily achieved with the help of Summarize tool.

Then join the source and Summarize tool to get your output.

Many thanks

Shanker V

ShankerV

Hi @Sshasnk

Here is the desired output for you.

My workflow:

Many thanks

Shanker V

davidskaife

Hi @Sshasnk

Here is a way of doing it:

Firstly calculate the differences using two Multi-Row Formula tools, then filter out unwanted fields. Rename the calculated fields and create the "Difference" type field, then union back together and finally sort in order.