CODEWITHSUNDEEP
excelpivot-tableslicers
reggie86
reggie86

Reputation: 289

Multiple Calculated Columns with Filters within Single Pivot Table

I am working to create a Pivot Table in Excel that has multiple calculated fields that take into account whether a condition is true, but cannot figure out how to create this within a single Pivot Table.

Behind the scenes, I have a set of data with a column that can have these values: A,B,C,D,F,L, or R. I have another column that is a dollar value, and another for Quantity. I am trying to calculate the price per unit when the Category is A,B, or C, but also calculate the price per unit when the Category is D.

I can create multiple Pivot Tables and tie them to the same slicer, but the issue is this: I need to display the spending in each of the two categories for each location, and when I create two separate Pivot Tables for this, locations that do not have spending in one of the categories are excluded (in this below case, location 2 has no spending in Category D, so it does not show up in the second Pivot Table).

Here is a portion of my data set. The whole data set is over 100,000 rows and will change over time, so I do need a solution for the long-term.

Location Category Volume Quantity

1 A $120.32 6000

3 A $30.08 1300

3 A $60.16 2600

1 B $39.91 1000

2 B $318.50 13000

2 C $196.00 8000

1 D $220.50 8100

3 D $171.50 6300

3 D $35.90 1000

3 D $53.85 1500

2 F $416.50 0

1 L $24.50 0

2 L $30.08 0

1 R $55.13 0

2 R $55.13 0

3 R $110.26 0

Thanks in advance for your help and let me know if I need to clarify anything!

Upvotes: 0

Views: 1923

Answers (1)

reggie86
reggie86

Reputation: 289

Alright, a little more digging yielded the answer. A slicer should be included that has all locations selected, then it should be tied to all Pivot Tables in use. For all Pivot Tables, go to Field Settings, then click on the Layout and Print Tab. Check the box 'Show items with no data'. Then go to Pivot Table Options and select the values you would like to display for blanks and errors.

I found the solution here.

Upvotes: 0

Related Questions