2 Replies Latest reply on Aug 7, 2018 6:34 AM by Yuehan Guo

    Calculated Measure: cannot mix aggregate and non-aggregate comparisons or results in 'if' expressions

    Yuehan Guo

      Hello,

       

      I'm creating a calculated measure using the formula below, but I'm getting an error message:

       

      if [Baseline] = [Para_Baseline1] AND [Para_Baseline1] = 'Actual' THEN [Baseline 1 YTD ]

      else [Baseline 1 FY Staging]

      end

      'cannot mix aggregate and non-aggregate comparisons or results in 'if' expressions'

       

      (ps: the formula for [Baseline 1 FY Staging] is if [Baseline]= [Para_Baseline1] AND [Para_Month]= [Comment_Date] THEN [Value *] else 0 end)

       

      What I'm trying to do is to create a measure that always show the full year amount. Para_Baseline1 is a parameter that allows a user to select between versions: Actual, Budget, Forecast1, etc.

      The Actual version only has months up till the current reporting month. Other versions always have the full 12 months for the entire year.

      Therefore, this measure should reflect YTD amount when Actual is selected, and FY amount when other versions are selected.

       

      Anyone has any idea how to fix it?

       

      Thanks,