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

    Elizabeth Navas



      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!