Recompute figures in the Value area section of a Pivot Table after receiving a user input

Assume a simple 5 column database with the following data

1. Circle Name – A text field
2. PO_Number – An is an alphanumeric field
3. Quantity sold – A numeric field
4. Unit Price – A numeric field denominated in US$
5. Revenue – A numeric field which is computed as Quantity sold * Unit Price

To determine the Circle and PO Number wise Quantity sold and Revenue, one can drag the first two fields to the Row labels and drag the third & fifth fields to the Value area section of a Pivot Table.

Now one may want to additionally view the Revenue in one of the following additional currencies – GBP and EUR.  So once the user selects, say GBP, he should additionally be able to choose from one among three cases – Base, Optimistic and Pessimistic.  Once the case is selected, the additional column so created should multiply the Revenue (in US$) with the exchange rate for the selected case.

I can think of three ways to workaround this issue:

1. Create additional columns in the Base data sheet – So there will be 6 additional columns that will get created in the Base data sheet

a. GBP – Base
b. GBP – Optimistic
c. GBP – Pessimistic
d. EUR – Base
e. EUR – Optimistic
f.  EUR – Pessimistic

Once these columns are created, one can readjust the Pivot Table source data range (to make these additional 6 columns appear in the Pivot Table Field List) and then check the columns which need to be viewed in the Pivot Table.  The challenge with implementing this method is that for a large dataset, say 50,000 rows, 300,000 cells with formulas (albeit simple multiplications) will need to be used.  This will add to file size.

2. Write calculated Field formulas in the Pivot Table itself – One can write 6 calculated Field formulas – one for each currency – case combination and then drag the desired fields in the Pivot Table.  The challenge with implementing this method is that if one wants to edit the exchange rate, then one will have to edit the calculated field formula (This is because, in calculated field formulas, one cannot refer to cells/ranges/named ranges) which is not really that intuitive/straight forward.

3. Use Power Pivot to fetch exchange rates from cells and allow the user to select Currencies and Cases via slicers – With the help of simple Power Pivot DAX formulas and slicers, one can resolve both problems mentioned above.

You may refer to my solution in this workbook (this solution is only for those using the PowerPivot tool).