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:

Year	Month	Forecast-Actuals	Revenue
2018	Jan	Forecast	$800
2018	Feb	Forecast	$800
2018	Mar	Forecast	$600
2018	Apr	Forecast	$80
2018	May	Forecast	$80
2018	Jun	Forecast	$80
2018	Jul	Forecast	$180
2018	Aug	Forecast	$180
2018	Sep	Forecast	$180
2018	Oct	Forecast	$180
2018	Nov	Forecast	$900
2018	Dec	Forecast	$900
2018	Jan	Actuals	$950
2018	Feb	Actuals	$1000
2018	Mar	Actuals	$625
2018	Apr	Actuals
2018	May	Actuals
2018	Jun	Actuals
2018	Jul	Actuals
2018	Aug	Actuals
2018	Sep	Actuals
2018	Oct	Actuals
2018	Nov	Actuals
2018	Dec	Actuals

<tbody>
</tbody>

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