Issues while using Pivot with different granularity

Hi All,

I have a scenario in which the fact's ( i.e in this case Actuals and Targets are derived using a formula). The roll-up of the sum of the actuals/targets at the least granular level i.e level4 is not equal to the value at level3, since the formula is being applied while deriving it. Using Excel pivot, is not giving me the required results as it just roll-up from the granular level upwards.

Have attached the excel workbook having the sample data and the expected output.

Any solutions to solve the issue, would be appreciated.

Thanks