3 Replies Latest reply on Oct 14, 2015 1:39 PM by Cherie Johnson

    Calculated Field with Dimension filter in numerator and denominator

    Cherie Johnson

      I created a text field “Target_Actual” to flag the type of metric, so that I could easily filter and distribute my data by that in a report.   I have many metrics with a "Target" number and an "Actual" Number.   I need to calculate a "% to Target" for each, so Actual Metric1/Target Metric1.

       

      However, in trying to create a Calculated metric for this it's not working.

      1) “% to Target Clients Logins”:  (this does not work)

       

       

      (IF [Target_Actual] = "Actual"

       

         THEN [Client Logins] ELSE 0 END)

       

      /

       

      (IF [Target_Actual] = "Target"

       

         THEN [Client Logins] ELSE 0 END)

       

       

      2) “% to Target Clients Logins2”:  (this works – Why does it let me filter the numerator on the Target_Actual Dimension, but I had to create a separate field for the denominator (Target)?

       

       

      (

       

      IIF( [Target_Actual] = "Actual",

       

        [Client Logins],0 )

       

      /

       

        [Target Client Logins]

       

      )

       

      How else could I do this with the "Target_Actual" Dimension?  Or do I have to create a separate field in my data source for every Actual Metric and every Target Metric?