
1. Re: How to filter a row section if the total difference is negative
Bill Lyons Nov 26, 2016 2:51 PM (in response to Carles Pujol)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.

filter group total example.twbx 29.7 KB
