Hi All,
My questions has to do with how one could achieve a cumulative summary based on one of the 'group by' columns. Here's what I mean:
Let's say I have the following data:
Item | Day | Total |
1 | 1 | 2 |
1 | 2 | 4 |
1 | 3 | 6 |
1 | 4 | 8 |
1 | 5 | 10 |
1 | 6 | 12 |
1 | 7 | 14 |
I added the following column to group the 'day' column into ranges. So any day within the first two days is in the '2' group. Any day within the first 5 days is int he '5' group, and any day within the first 10 days is in the '10' group, like so:
Item | Day | Total | Day Group |
1 | 1 | 2 | 2 |
1 | 2 | 4 | 2 |
1 | 3 | 6 | 5 |
1 | 4 | 8 | 5 |
1 | 5 | 10 | 5 |
1 | 6 | 12 | 10 |
1 | 7 | 14 | 10 |
Then I summarized the table based on the Item and Day group columns, taking the sum of the 'total' column:
Item | Day Group | Total |
1 | 2 | 6 |
1 | 5 | 24 |
1 | 10 | 26 |
However, what I want is for my totals to include to sums from the prior day group. In other words, the total for the '5' day group should include the total from the '1' group, and the total from the '10' group should include the totals from both '1' and '5', like so:
Item | Day Group | Total |
1 | 2 | 6 |
1 | 5 | 32 |
1 | 10 | 58 |
How can this be done?
Thanks in advance!
Solved! Go to Solution.
Probably the easiest way to do this is with the Running Total tool. You can Group by your product and create a Running Total of your Total column, and it will add in the order it receives the data, creating a new column. Then, a Select tool can be used to deselect the old total and rename the RunTotal_ column.
You could also do this with a Multi-Row Formula, but this configuration is a little more complex (although more powerful), so I'd suggest trying out Running Total first.