5 Replies Latest reply: Nov 2, 2015 2:43 AM by Rene Iversen RSS

    Exclude 0-values in pivot-table conditional colour, when comparing values.

    Rene Iversen

      .. Yeah i really didn't know what to call this thread..

       

      Business Case: Compare values from user to a benchmark. Colour values red() if value is lower than benchmark - pretty simple.

       

      My problem is this:

       

      i have a pivot table with values for user A. These values are to be compared to a Benchmark group (known as User X). However, when User A only have values from 2010-2015, but user X has values from 2005-2015 i get a lot of meaningless columns with zero-values in 2005-2009 due to the conditional colour expression below:

       

      ----

      if(

      sum(Amount)

       

      <

       

      sum(

      {<

      User = {'User X'}

      >}

      Amount), red())

      ----

       

      If i remove the colour expression i obviously only get data for the years i want, and as such (and since it is not a measure by itself) i really didn't think that by adding a colour expression, i would add data to the pivot table... I just need to get rid of it again..

       

      I tried multiple variations of if-statements and set expressions, but can't seem to get it done - Help please

       

      Thanks.