4 Replies Latest reply: Aug 11, 2015 12:09 PM by Vivek Singh RSS

    Cumulative and Rangesum() Color Conditioning

      Using data that is reported on a month to month basis, I am trying to color condition the data based on the following:

      =
      IF(
      Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=today(DATE)) <=$(=Max(DATE))"}>}[Sales Data.Act/Fcst]), vColorFORECAST,

      IF(
      (
      Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Sales Data.Act/Fcst])
      -
      Sum({1<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Sales Data.Plan])) < 0, vColorACTUALRED,

      IF(
      (
      Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Sales Data.Act/Fcst])
      -
      Sum({1<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Sales Data.Plan])) >= 0, vColorACTUALGREEN)))

       

       

      However, my color conditions are only comparing each months Act/Fcst less Plan, as opposed to the cumulative value up to the date in question. I have since tried the following, but have yet to format the colors correctly. any and all help is much appreciated. Best.

       

      =
      IF(
      Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=today(DATE)) <=$(=Max(DATE))"}>}[Sales Data.Act/Fcst]), vColorFORECAST,

      IF(
      Rangesum(Above(Sum({$<[Sales Data.Metric]={'Direct Revenue'}>}[Sales Data.Act/Fcst]), 0, RowNo()))
      -
      Rangesum(Above(Sum({1<[Sales Data.Metric]={'Direct Revenue'}>}[Sales Data.Plan])), 0, RowNo()) < 0, vColorACTUALRED,

      IF(
      Rangesum(Above(Sum({$<[Sales Data.Metric]={'Direct Revenue'}>}[Sales Data.Act/Fcst]), 0, RowNo()))
      -
      Rangesum(Above(Sum({1<[Sales Data.Metric]={'Direct Revenue'}>}[Sales Data.Plan])), 0, RowNo()) >= 0, vColorACTUALGREEN)))