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?