broncosrul
New Member
- Joined
- May 3, 2012
- Messages
- 25
I have a set of data that is 4 items divided into 2 categories and then monthly sales over 2 years. An example of the data is:
<tbody>
</tbody>
And I can easily pivot into this:
<tbody>
</tbody>
Then I inserted a calculated item for the % change. The formula being =(2017-2016)/2016 and it looks fine.
<tbody>
</tbody>
However, if I add the category, I end up with lines that don't make any sense. Now I have calculated items for balls in the Game Category and vice versa. And these are not lines anywhere in the data, so I am not sure where they are coming from. Can anyone tell me what is happening and how to fix it? And if it can't be fixed, if there is a workaround. You can see below what I am seeing:
<tbody>
</tbody>
Thanks in advance
Travis
Category | Item Description | Month | Year | Units Sold | Revenue |
Balls | Basketball | April | 2016 | 20 | 160 |
Balls | Baseball | April | 2016 | 40 | 144 |
Games | Monopoly | April | 2016 | 60 | 900 |
Games | Trouble | April | 2016 | 30 | 300 |
Balls | Basketball | April | 2017 | 12 | 96 |
Balls | Baseball | April | 2017 | 61 | 276 |
Games | Monopoly | April | 2017 | 14 | 210 |
Games | Trouble | April | 2017 | 25 | 250 |
Balls | Basketball | August | 2016 | 15 | 120 |
Balls | Baseball | August | 2016 | 30 | 112 |
Games | Monopoly | August | 2016 | 45 | 675 |
Games | Trouble | August | 2016 | 23 | 230 |
Balls | Basketball | August | 2017 | 30 | 240 |
Balls | Baseball | August | 2017 | 50 | 44 |
Games | Monopoly | August | 2017 | 24 | 360 |
Games | Trouble | August | 2017 | 70 | 700 |
<tbody>
</tbody>
And I can easily pivot into this:
Sum of Revenue | Month | ||||||||||||
Item Description | Year | January | February | March | April | May | June | July | August | September | October | November | December |
Baseball | 2017 | 216 | 40 | 88 | 220 | 100 | 172 | 28 | 188 | 212 | 116 | 64 | 144 |
2016 | 36 | 160 | 176 | 144 | 160 | 128 | 144 | 112 | 120 | 88 | 96 | 64 | |
Basketball | 2017 | 184 | 336 | 368 | 344 | 216 | 80 | 504 | 552 | 264 | 472 | 400 | 432 |
2016 | 160 | 176 | 144 | 160 | 128 | 144 | 112 | 120 | 88 | 96 | 64 | 72 | |
Monopoly | 2017 | 495 | 225 | 330 | 570 | 75 | 375 | 600 | 135 | 870 | 465 | 855 | 585 |
2016 | 900 | 990 | 810 | 900 | 720 | 810 | 630 | 675 | 495 | 540 | 360 | 405 | |
Trouble | 2017 | 640 | 670 | 480 | 200 | 60 | 80 | 600 | 290 | 380 | 520 | 250 | 170 |
2016 | 300 | 330 | 270 | 300 | 240 | 270 | 210 | 230 | 170 | 180 | 120 | 140 |
<tbody>
</tbody>
Then I inserted a calculated item for the % change. The formula being =(2017-2016)/2016 and it looks fine.
Sum of Revenue | Month | ||||||||||||
Item Description | Year | January | February | March | April | May | June | July | August | September | October | November | December |
Baseball | 2017 | 216 | 40 | 88 | 220 | 100 | 172 | 28 | 188 | 212 | 116 | 64 | 144 |
2016 | 36 | 160 | 176 | 144 | 160 | 128 | 144 | 112 | 120 | 88 | 96 | 64 | |
Change | 500% | -75% | -50% | 53% | -38% | 34% | -81% | 68% | 77% | 32% | -33% | 125% | |
Basketball | 2017 | 184 | 336 | 368 | 344 | 216 | 80 | 504 | 552 | 264 | 472 | 400 | 432 |
2016 | 160 | 176 | 144 | 160 | 128 | 144 | 112 | 120 | 88 | 96 | 64 | 72 | |
Change | 15% | 91% | 156% | 115% | 69% | -44% | 350% | 360% | 200% | 392% | 525% | 500% | |
Monopoly | 2017 | 495 | 225 | 330 | 570 | 75 | 375 | 600 | 135 | 870 | 465 | 855 | 585 |
2016 | 900 | 990 | 810 | 900 | 720 | 810 | 630 | 675 | 495 | 540 | 360 | 405 | |
Change | -45% | -77% | -59% | -37% | -90% | -54% | -5% | -80% | 76% | -14% | 138% | 44% | |
Trouble | 2017 | 640 | 670 | 480 | 200 | 60 | 80 | 600 | 290 | 380 | 520 | 250 | 170 |
2016 | 300 | 330 | 270 | 300 | 240 | 270 | 210 | 230 | 170 | 180 | 120 | 140 | |
Change | 113% | 103% | 78% | -33% | -75% | -70% | 186% | 26% | 124% | 189% | 108% | 21% |
<tbody>
</tbody>
However, if I add the category, I end up with lines that don't make any sense. Now I have calculated items for balls in the Game Category and vice versa. And these are not lines anywhere in the data, so I am not sure where they are coming from. Can anyone tell me what is happening and how to fix it? And if it can't be fixed, if there is a workaround. You can see below what I am seeing:
Sum of Revenue | Month | |||||||||||||
Category | Item Description | Year | January | February | March | April | May | June | July | August | September | October | November | December |
Balls | Baseball | 2017 | 216 | 40 | 88 | 220 | 100 | 172 | 28 | 188 | 212 | 116 | 64 | 144 |
2016 | 36 | 160 | 176 | 144 | 160 | 128 | 144 | 112 | 120 | 88 | 96 | 64 | ||
Change | 500% | -75% | -50% | 53% | -38% | 34% | -81% | 68% | 77% | 32% | -33% | 125% | ||
Basketball | 2017 | 184 | 336 | 368 | 344 | 216 | 80 | 504 | 552 | 264 | 472 | 400 | 432 | |
2016 | 160 | 176 | 144 | 160 | 128 | 144 | 112 | 120 | 88 | 96 | 64 | 72 | ||
Change | 15% | 91% | 156% | 115% | 69% | -44% | 350% | 360% | 200% | 392% | 525% | 500% | ||
Monopoly | Change | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | |
Trouble | Change | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | |
Balls Total | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | ||
Games | Baseball | Change | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! |
Basketball | Change | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | |
Monopoly | 2017 | 495 | 225 | 330 | 570 | 75 | 375 | 600 | 135 | 870 | 465 | 855 | 585 | |
2016 | 900 | 990 | 810 | 900 | 720 | 810 | 630 | 675 | 495 | 540 | 360 | 405 | ||
Change | -45% | -77% | -59% | -37% | -90% | -54% | -5% | -80% | 76% | -14% | 138% | 44% | ||
Trouble | 2017 | 640 | 670 | 480 | 200 | 60 | 80 | 600 | 290 | 380 | 520 | 250 | 170 | |
2016 | 300 | 330 | 270 | 300 | 240 | 270 | 210 | 230 | 170 | 180 | 120 | 140 | ||
Change | 113% | 103% | 78% | -33% | -75% | -70% | 186% | 26% | 124% | 189% | 108% | 21% | ||
Games Total | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! |
<tbody>
</tbody>
Thanks in advance
Travis