8 Replies Latest reply: May 19, 2015 3:20 AM by James Kerrigan RSS

    Trouble with if & count statements in KPI chart

    James Kerrigan

      Hey guys,

       

      I'm trying to do a couple things with the If statement which I'm having trouble with, but will replace dimensions with colours to simplify.

       

      I'm trying to count how many colours have a difference higher than 50% and put this in a KPI chart. So if here are 3 out of 4 colours with a % over 50%, I want it to say 3.

       

      Each colour has activities associated with it, and each activity has a low and a high number. I divide low by high to work out a difference, and I want to count how many colours have a difference higher than 50%. So if the colour Red has 5 activities, with these totalling 100 for high and 60 for low, this would be 60%.

       

      So the logic should be something like

      If(Sum(Low) / (Sum(High) > 0.5, Count(Colour)

       

      But that doesn't work for obvious reasons, because I want it for each individual colour, not the total.

       

      Does anyone know how to calculate this?

       

      Thanks in advance.