Calculated Item Causing issues in Pivot Table

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:

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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi Travis,

I always have had problems with CALCULATED ITEMS too.

I suggest that you use Power Query to add a calculated item for %change

1) Load the table
2) Pivot the Column Year using Revenues as Values
3) Use "Group by" to
4) Add a calculated Column for change
5) Then Unpivot


Have you used PQ before?
 
Last edited:
Upvote 0
I haven't used Power Query before and I don't think I have it available. I know Microsoft split out Power Pivot and only certain versions of 2016 come with the addins. When I try to enable the addins, I don't have any in the COM addins where I would expect to see these. So, my guess is that my company is using one of the versions of 2016 that doesn't include Power Pivot and Power Query. So, I don't know a workaround to being able to use it.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top