Hopeless Cub Fan
New Member
- Joined
- May 25, 2011
- Messages
- 31
I am trying to create aggregate positive and negative variance buckets based on my Power Pivot dataset. The basic steps I am trying to achieve are below:
I am sure this isn't very difficult, but I've already spend 6+ hours trying to get it on my own. Thanks in advance for any help you can offer!
Excel 2010
<tbody>
</tbody>
- Step 1: Calculate a variance at an aggregate level, in this example “Group”
- Step 2: Look at the variance associated with each group and place it into either a “Positive” or “Negative” variance bucket
- Step 3: Be able to have all the associated variance roll up under either the “Positive” or “Negative” bucket
I am sure this isn't very difficult, but I've already spend 6+ hours trying to get it on my own. Thanks in advance for any help you can offer!
Excel 2010
A | B | C | D | E | F | G | H | I | J | |
---|---|---|---|---|---|---|---|---|---|---|
1 | Step 1: Base Data | Step 2: Sorted | Step 3: Desired Result | |||||||
2 | Group | Variance | Variance Grouping | Variance Grouping | Group | Variance | Variance Grouping | Variance | ||
3 | A | -10 | Negative | Positive | E | 42 | Positive | 81 | ||
4 | B | 25 | Positive | Positive | B | 25 | Negative | -23 | ||
5 | C | 3 | Positive | Positive | I | 10 | Total | 58 | ||
6 | D | -3 | Negative | Positive | C | 3 | ||||
7 | E | 42 | Positive | Positive | F | 1 | ||||
8 | F | 1 | Positive | Positive | G | 0 | ||||
9 | G | 0 | Positive | Negative | D | -3 | ||||
10 | H | -10 | Negative | Negative | A | -10 | ||||
11 | I | 10 | Positive | Negative | H | -10 | ||||
12 | Total | 58 | ||||||||
13 | ||||||||||
14 | * Groups above are aggregations of detailed data |
<tbody>
</tbody>
Sheet1