I have an Excel table in the following format:
| Product | Subclass | Year-over-Year (YoY) | Current Year Sales | Previous Year Sales |
|---|---|---|---|---|
| Apple | 1 | +50% | 30 | 20 |
| Apple | 2 | 25% | 50 | 40 |
| Apple | 3 | -10% | 76 | 80 |
From this I generate a pivot table and consolidate the results on the 'Product' (Apple):
| Product | Year-over-Year (YoY) |
|---|---|
| Apple | ??? |
Solutions like for example here did not provide an answer for my case to correctly calculate the YoY (+11.4%) across the board for all apple products.
Hence: How can I calculate the YoY correctly in my situation in a pivot table?
Assuming you created a Pivot Table without adding the data to the 'data model': use PivotTable Analyze->Fields, Items, & Sets->Calculated Field. Change the FieldName to 'YOY Growth, In formula type the expression: =('Current Year Sales'-'Previous Year Sales' )/'Previous Year Sales'
and enter.