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())

       

       

      Detail:

          

       

      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.

       

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

       

       

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

       

      Thanks in Advance