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

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

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.