Pivot table: calculated item

rossa

Board Regular
Joined
Nov 7, 2013
Messages
109
Hi. I have a pivot table where I am analysing sales for current and prior year quarters for a number of different companies. Three companies, each with three products unique to them. When setting up the pivot the total would return the total of both columns so I added a calculated item so I could see the difference.
The problem I'm having is that the pivot now assumes that each product is at each company and returns 0 value for products under a company that the product is not held in, see screen shot below. Is there a way to remove these "non existing" products?
 

Attachments

  • Capture.JPG
    Capture.JPG
    65.8 KB · Views: 8

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
Have you tried a filter?
 

rossa

Board Regular
Joined
Nov 7, 2013
Messages
109
I think a filter would only work in conjunction with a formula like "AND' where you can identify something that is 0 in both quarters. It may also lead to a situation where you could mistakenly hide something.
 

Forum statistics

Threads
1,143,841
Messages
5,721,110
Members
422,340
Latest member
canadianbacon357

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
Top