Pivot Table Help Please: Calculating ‘% Achieved Cumulative’ in a pivot table

[jhDR_]

Please could someone help me with how to approach calculating ‘% Achieved Cumulative’ in a pivot table?
Have search online without success.

I am trying to create a Actuals vs. Forecast pivot table to show the following metrics:

• Actual value for month
• Cumulative Actual YTD
• Forecast value for month
• Cumulative Forecast YTD
• % Achieved Month (Actual/Forecast)
• % Achieved Cumularive (Cumulative Actual/Cumulative Forecast)

I’d like to keep this all in a pivot table if possible as the data changes regularly.

My data table looks like this - repeated for each line of business:

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Forecast-Actuals[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Jan[/TD]
[TD]Forecast[/TD]
[TD]$800[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Feb[/TD]
[TD]Forecast[/TD]
[TD]$800[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Mar[/TD]
[TD]Forecast[/TD]
[TD]$600[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Apr[/TD]
[TD]Forecast[/TD]
[TD]$80[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]May[/TD]
[TD]Forecast[/TD]
[TD]$80[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Jun[/TD]
[TD]Forecast[/TD]
[TD]$80[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Jul[/TD]
[TD]Forecast[/TD]
[TD]$180[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Aug[/TD]
[TD]Forecast[/TD]
[TD]$180[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Sep[/TD]
[TD]Forecast[/TD]
[TD]$180[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Oct[/TD]
[TD]Forecast[/TD]
[TD]$180[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Nov[/TD]
[TD]Forecast[/TD]
[TD]$900[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Dec[/TD]
[TD]Forecast[/TD]
[TD]$900[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Jan[/TD]
[TD]Actuals[/TD]
[TD]$950[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Feb[/TD]
[TD]Actuals[/TD]
[TD]$1000[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Mar[/TD]
[TD]Actuals[/TD]
[TD]$625[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Apr[/TD]
[TD]Actuals[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]May[/TD]
[TD]Actuals[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Jun[/TD]
[TD]Actuals[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Jul[/TD]
[TD]Actuals[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Aug[/TD]
[TD]Actuals[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Sep[/TD]
[TD]Actuals[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Oct[/TD]
[TD]Actuals[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Nov[/TD]
[TD]Actuals[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]Dec[/TD]
[TD]Actuals[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So far I’ve been able to achieve the first 5 metrics:

• Actual value for month - “Sum of Revenue / Actual”
• Cumulative Actual YTD – “Show Values As Running Total in Month”
• Forecast value for month – “Sum of Revenue / Forecast”
• Cumulative Forecast YTD - “Show Values As Running Total in Month”
• % Achieved Month (Actual/Forecast) – “Show Values As % of Forecast-Actuals Forecast”

It’s the final metric that I’m stuck on (though I am open to recommendations on a better all-round solution)

Please could someone could someone help me on how to approach a ‘% Achieved Cumulative’ row in a pivot table?

Thank you.

 

[Worf]

Welcome to the Board

Seems that you need a calculated field or item:

http://www.contextures.com/calculatedfieldcalculateditem.html