5 Replies Latest reply: Feb 23, 2017 5:44 AM by Tom Neal RSS

    Chart Expression High Level Detail Showing Null

    Tom Neal

      Hi All,


      I am trying to show the percentage of people who are present within the boundaries of greater than 0.5 but less that 5.

      This is within a pivot table.

      The first statement below works when the chart is expanded to the lowest level of detail.  However when the detail is collapsed only a null appears.


      Expression (Present %)


      if (SUM({<[Present]={"True"}>}[Number])/SUM(People)*100 > 0.5
      and SUM({<[Present]={"True"}>}[Number])/SUM(People)*100 < 5,
      SUM({<[Present]={"True"}>}[Number])/SUM(People), Null())






      High Level:

      I need the High level value to show the average of the rows with values i.e. 0.50 and 0.58


      I have been playing around with the aggregation function but with no joy.


      if(sum(sum({<[Present]={"True"}>}[Number])/Sum(People)*100) < 5
      and sum(sum({<[Present]={"True"}>}[Number])/Sum(People)*100) > 0.5,



      Could someone point me in the right direction as to where I'm going wrong?


      Thanks in Advance