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.