I hope anyone can give me a hand with a formula error that I have not been able to fix. I am trying to calculate the Weighted Average CV that is pretty much something like this:
Delta = ABS (Weighted Average CPI - CPI) where Weighted Average CPI has been calculated in tableau like this:
WINDOW_AVG(SUM([ACTUAL MH]*[CPI])/SUM([ACTUAL MH])) where ACTUAL MH and CPI are columns of values listed in Excel.
Once the Delta has been calculated, the following formulas should be calculated like this:
Weight Delta = ACTUAL MH * Delta
Here is where I have the error since Delta is an aggregated formula and ACTUAL MH is a value that corresponds to a column of values in Excel. If I can fix this formula in somehow, the following formulas should look like this:
Weighted Average Delta = SUM(Weight Delta)/SUM(Actual MH)
Weighted Average CV = Weighted Average Delta/Weighted Average CPI
I have attached the excel spreadsheet (Consolidated tab) in the case anyone needs to take a look of the values ....
Any help will be very much appreciated! Thanks!
Do you still need assistance with this? If so, the following KB article should help explain the issue: