Formula or Tool similar to an Excel Pivot Table - calculated field/item

[Stuart_C]

Hi,

I'm trying to find a function or tool that's similar the calculated field/item in an Excel Pivot Table.  My current data has Actuals and budget in the "Type" column and I'm trying to calculate the variance(budget - actuals) and add this as a row in the "Type" column.  There are 3 different scenarios:

1)  There is a budget and actual #'s by department, account name, vendor, and date

2)  There is a budget but no actuals #'s by department, account name, vendor, and date

3)  There is no budget but there are actuals by department, account name, vendor, and date

Data

Type	Expense Category	Department	Account Name	Vendor	Date	Amount
Actuals	Meals	HR	605 - Lunch	McDonalds	1/1/2020	25.00
Budget	Meals	HR	605 - Lunch	McDonalds	2/7/2020	30.00
Actuals	Meals	HR	606 - Dinner	Carl's JR	1/3/2020	75.00
Budget	Meals	HR	604 - Breakfast	Wendy's	4/5/2020	10.00
Actuals	Meals	IT	606 - Dinner	Subway	1/4/2020	80.00
Budget	Meals	IT	604 - Breakfast	Subway	7/8/2020	13.00
Actuals	Hotel	HR	505 - 1 Bedroom	Hyatt	2/4/2020	247.00
Budget	Hotel	HR	505 - 1 Bedroom	Hyatt	6/8/2020	136.00
Actuals	Hotel	HR	506 - 2 Bedroom	Marriott	4/4/2020	245.00
Budget	Hotel	HR	507 - 3 Bedroom	Motel 5	3/11/2020	303.00
Actuals	Hotel	IT	506 - 2 Bedroom	Red Rock	7/9/2020	265.00
Budget	Hotel	IT	507 - 3 Bedroom	Red Rock	10/11/2020	457.00

Output

Type	Expense Category	Department	Account Name	Vendor	Date	Amount
Actuals	Meals	HR	605 - Lunch	McDonalds	1/1/2020	25.00
Budget	Meals	HR	605 - Lunch	McDonalds	2/7/2020	30.00
Actuals	Meals	HR	606 - Dinner	Carl's JR	1/3/2020	75.00
Budget	Meals	HR	604 - Breakfast	Wendy's	4/5/2020	10.00
Actuals	Meals	IT	606 - Dinner	Subway	1/4/2020	80.00
Budget	Meals	IT	604 - Breakfast	Subway	7/8/2020	13.00
Actuals	Hotel	HR	505 - 1 Bedroom	Hyatt	2/4/2020	247.00
Budget	Hotel	HR	505 - 1 Bedroom	Hyatt	6/8/2020	136.00
Actuals	Hotel	HR	506 - 2 Bedroom	Marriott	4/4/2020	245.00
Budget	Hotel	HR	507 - 3 Bedroom	Motel 5	3/11/2020	303.00
Actuals	Hotel	IT	506 - 2 Bedroom	Red Rock	7/9/2020	265.00
Budget	Hotel	IT	507 - 3 Bedroom	Red Rock	10/11/2020	457.00
Variance	Meals	HR	605 - Lunch	McDonalds	2020	5.00
Variance	Meals	HR	606 - Dinner	Carl's JR	2020	(75.00)
Variance	Meals	HR	604 - Breakfast	Wendy's	2020	10.00
Variance	Meals	IT	606 - Dinner	Subway	2020	(80.00)
Variance	Meals	IT	604 - Breakfast	Subway	2020	13.00
Variance	Hotel	HR	505 - 1 Bedroom	Hyatt	2020	(111.00)
Variance	Hotel	HR	506 - 2 Bedroom	Marriott	2020	(245.00)
Variance	Hotel	HR	507 - 3 Bedroom	Motel 5	2020	303.00
Variance	Hotel	IT	506 - 2 Bedroom	Red Rock	2020	(265.00)
Variance	Hotel	IT	507 - 3 Bedroom	Red Rock	2020	457.00

Thank you,

- Stuart

[echuong1 · Accepted solution]

You can take advantage of the cross-tab and transpose tools to "pivot" your data for the calculations.

See attached for an example.

[Stuart_C]

Thank you @echuong1 !  I'm receiving [Null] values in some cells in the Actuals and Budget column so it's calculating a [Null] for the variance.  Could you please help me correct this?

[echuong1]

Are you able to upload a copy of the data you're using?

The data cleansing tool should take care of any nulls, and I don't see any on my side. 

[Stuart_C]

I figured it out.  I forgot to check the Actuals and Budget fields in the Data Cleansing tool.  Everything works perfectly!  Thank you!