1 Reply Latest reply on Nov 26, 2016 2:51 PM by Bill Lyons

    How to filter a row section if the total difference is negative

    Carles Pujol

      How can I filter a row section if the subTotal difference is negative as illustrated below. Any ideas?

       

      Thanks

        • 1. Re: How to filter a row section if the total difference is negative
          Bill Lyons

          The solution attached solves this with three calculated fields (these could be combined together into a single calculated field, but I separated them for clarity). First, since your subtotal "difference" (what I call "net") is the yellow subtotal minus the blue subtotal, I created a calculated field which returns negative values for blue and positive for yellow:

          [Value (blue negative)]:

          IF [Team] = 'Blue' THEN - [Value]

          ELSE [Value]

          END

           

          The next step is a Level of Detail calculation, summing the values (with blue negative) at the desired level, in this case, Name and Date:

          [Name Date Net]:

          { FIXED [Name], [Date] : sum([Value (blue negative)]) }

           

          The third calculation returns true if the net is negative:

          [Net is negative]:

          [Name Date Net] < 0

           

          Then, drop [Net is negative] on the Filter shelf, and include only false values.

           

          In the attached example (in v10.1), Sheet 1 shows the original values like your screen capture. Sheet 2 shows the LoD calculation. And Sheet 3 is the result with the filter applied.

           

          Is that what you are looking for? For future reference, this would probably have had a much quicker response if you had included a packaged workbook. For future reference, please see Packaged workbooks: when, why, how.