smart cubes
New Member
- Joined
- Mar 27, 2018
- Messages
- 4
Here is a highly simplistic data set:
How would I make it so that in my pivot table, in the values section, I could break it out into multiple values (such as Budget compared against Actual)? I'd like to compare Budget count to Actual count with the ability to break it out by department and grade. And how would I get the pivot table to automatically calculate the variance between the two?
How would I do that? How would I compare data categories in the same column in a pivot table? I've seen it done before, but I'm just not sure how to do it.
So that my pivot table looks like this:
Right now, I can't get the pivot table to calculate variances between both data types. Instead it sums them. When I right click the pivot table and choose "show values as" it doesn't have an option for variance. When I select "summarize value field by", then "more options" the "var" menu fills my pivot table with #DIV/0! errors
Any help is appreciated
| Type | Month | Count | Department | Grade |
| Budget | February | 1 | Marketing | 1 |
| Budget | February | 1 | Operations | 2 |
| Budget | February | 1 | Marketing | 3 |
| Budget | February | 1 | Operations | 4 |
| Actual | February | 1 | Marketing | 2 |
| Actual | February | 1 | Marketing | 2 |
| Actual | February | 1 | Operations | 1 |
| Actual | February | 1 | Operations | 3 |
How would I make it so that in my pivot table, in the values section, I could break it out into multiple values (such as Budget compared against Actual)? I'd like to compare Budget count to Actual count with the ability to break it out by department and grade. And how would I get the pivot table to automatically calculate the variance between the two?
How would I do that? How would I compare data categories in the same column in a pivot table? I've seen it done before, but I'm just not sure how to do it.
So that my pivot table looks like this:
| Count of Type | Type | |||
| Department | Grade | Actual | Budget | Variance |
| Marketing | 1 | 1 | 1 | 0 |
2 | 1 | -1 | ||
3 | 1 | 1 | ||
| Marketing Total | 2 | 2 | ||
| Operations | 1 | 1 | -1 | |
2 | 1 | 1 | ||
3 | 1 | -1 | ||
4 | 1 | 1 | ||
| Operations Total | 2 | 2 | 4 | |
| Total | 4 | 4 | 8 |
Right now, I can't get the pivot table to calculate variances between both data types. Instead it sums them. When I right click the pivot table and choose "show values as" it doesn't have an option for variance. When I select "summarize value field by", then "more options" the "var" menu fills my pivot table with #DIV/0! errors
Any help is appreciated
