CODEWITHSUNDEEP
excelmathexcel-formulams-officepivot-table
Dom Vito
Dom Vito

Reputation: 567

Changing Average Formula in pivot table depending on filter

I have a group of data with the following fields: Code, Product, Rate, Volume

Each code has multiple products. On each row of the pivot table is a code, and the table has a product filter. The Rate is the value in the table. I want that rate to change depending on a combination of products selected.

E.G. If Code A has:

I want the rate field to display 2.4% when all are selected, 2.75% when A&B are selected, 1.5% when A&C are selected and 2.5% when B&C are selected.

How do I do this? Currently, I am only able to have the rate field display an average or a sum, which is not right. I.E. in the above example, average would display 2%, which is not accurate.

Upvotes: 0

Views: 1350

Answers (1)

Kit
Kit

Reputation: 341

Its a little convoluted, but I think this gets at what you want.

  1. In your original table of data, create a helper column that multiplies Rate by Volume. I will refer to this column as Helper.
  2. Select your pivot table, then go to Analyze>Calculations>Fields Items & Sets> Calculated Field enter image description here
  3. Name your new field Weighted Average then for your formula enter Helper/Volume, then select Add
  4. Your calculated field will now show the weighted average for each Code based on the filtered Products. It worked for your simple example, I would think it would work for your larger table.

Upvotes: 1

Related Questions