Hi Rene,

Can you please upload a sample file so we can see your question in context.

Thanks,

Mynda

Thanks for your reply Mynda.....I have a attached a copy of the file.

Sorry....I uploaded a better file......it is the one with the "2" at the end. What I would like to know is this:

1. Is it possible for the "Rate" field to change based on the Customer Name.

Hopefully this is clear.....if not....let me know.

Thanks

maybe not the solution you're looking for (don't know if that is possible, I doubt it): is it possible to add a new column to your raw data in which you use that if statement to make a rate and then make the pivot with that column/field an make your calculated field with it as mentioned?

Hi Rene,

I'm with Frans. I would do this calculation in the source data, not in a PivotTable calculated field.

There's more transparency this way and you could set up a table with your customers listed and their respective rates. This table could be used in a VLOOKUP formula in your source data to find the rate for the relevant customer and perform the calculation.

And if you wanted to change the rates it would be easy to do in the lookup table.

I hope that all makes sense, but if you have any questions please let us know.

Mynda

Ok.....thanks.

There is some extra costs involved in getting it into the source data.....a BI component needs to be added so we can pull in, and modify,  the source data automatically. That is why I was looking at the pivot table option.

Thanks so much for having a look at it for me, and the quick responses!

Don't understand you, I think.

When you import your data in Excel, you can already have an Excel-table ready where you import the data in.

In that table you have added an extra column with the formula.

You only have to refresh the Pivot table to let it work!