Compute the average of values against the 5 most recent dates of each Category


Here is a simple 3 column dataset showing Categories, Date and Value

Catagorie Date Value
Fish 08-12-2015 6
Crab 05-12-2015 7
Crab 04-12-2015 6
Bird 27-11-2015 4
Snow 25-11-2015 10
Cat 21-11-2015 7
Dog 12-11-2015 5
Dog 28-10-2015 5
Fish 12-10-2015 3
Bird 11-10-2015 9
Dog 22-09-2015 9
Crab 17-08-2015 8
Cat 11-08-2015 1
Fish 20-07-2015 5
Crab 03-07-2015 2
Fish 02-06-2015 8
Fish 26-05-2015 9
Dog 14-05-2015 4
Snow 07-05-2015 7
Bird 03-05-2015 9
Cat 20-04-2015 10
Cat 15-04-2015 2
Snow 13-04-2015 3
Crab 29-03-2015 5
Crab 23-03-2015 2
Bird 14-03-2015 5
Cat 14-03-2015 1
Dog 26-02-2015 9
Fish 16-02-2015 4
Fish 08-02-2015 6
Bird 18-01-2015 1
Snow 06-01-2015 10

The objective is to compute category wise average of values against the 5 most recent dates.  So the expected result is:

Categorie Category wise average values on 5 most recent dates
Bird 5.60
Cat 4.20
Crab 5.60
Dog 6.40
Fish 6.20
Snow 7.50

So for the Category of Fish (dates are sorted in descending order), the average should be 6.2 (average of 6,3,5,8,9)

Catagorie Date Value
Fish 08-12-2015 6
Fish 12-10-2015 3
Fish 20-07-2015 5
Fish 02-06-2015 8
Fish 26-05-2015 9
Fish 16-02-2015 4
Fish 08-02-2015 6

I have solved this problem using the PowerPivot. You may download my solution workbook from here.

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).