2 Replies Latest reply on Jun 17, 2012 10:30 AM by Joe Mako

    Subtotal needs to sum what is displayed, not what underlies

    Dave Rogers

      I have two columns in my Data…Sales and Profit.  My report has cumulative (over time) results, and needs to override the Profit number with a 0 if Sales is 0.  Using the following as sample data:

       

      Sales   Profit

      0           10

      1           15

      2           30

       

      My report needs to show

       

      Sales   Profit

      0           0

      1           15

      2           30

      Subtotal: 45

       

      The following works at the detail level:

       

      IF SUM([Sales]) = 0 THEN 0 ELSE sum([profit]) END

       

      However, the Subtotal row on my report applies the same logic…and, since at the subtotal level sum(Sales) > 0, then all profit values are totaled and I get 55 rather than 45.  Basically, my need in Excel terms is to have each cell above show profit or zero depending on Sales, then sum the values above it for t he Subtotal.  Is there a way I can control how Subtotals are presented in this way?