1 Reply Latest reply on Oct 28, 2013 4:42 PM by Tracy Rodgers

# Having problems with a formula that gives me the "cannot mix aggregation" all times

Hi,

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!

Elizabeth