2 Replies Latest reply on Aug 26, 2015 10:01 PM by Alex Martell

    Dynamic threshold in if statement

    Alex Martell

      I want a formula that does this:

       

      groupVarietalValid:

          IF {FIXED[Varietal] : SUM([Quantity]) } > 34 THEN [Varietal] ELSE "Other" END

       

      That works, but instead of 34 being a fixed number, I want it to be a dynamic number based on the total quantity for the table.

       

      So something like this:

       

      groupVarietalNotValid:

          IF {FIXED[Varietal] : SUM([Quantity]) } > TOTAL(SUM([Quantity])) * 21 / 144 THEN [Varietal] ELSE "Other" END


      When I do this (and countless other variations trying to get it to work), it gives me the "cannot mix aggregate etc." error


      Any help hugely appreciated.  I have attached my data and calculations.