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
Solved! Go to Solution.
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?
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.
I figured it out. I forgot to check the Actuals and Budget fields in the Data Cleansing tool. Everything works perfectly! Thank you!