How to exclude Order IDs which have a total of zero Costs when grouped by Project IDs

[Anonymous]

Hi,

I tried to find an answer to this from the existing discussions, but couldn't find a match.

From a bunch of different kinds of postings over a range of several years, I have successfully aggregated the Costs by Order IDs. Now I would need to exclude Order IDs for which the total Cost is zero when they are grouped by Project IDs.

In the example table below, the Order IDs A1 and A2 should be excluded, because the total Costs is zero when they are grouped by Project IDs (+5 -5 = 0). The Order IDs B1 and B2 should be visible, because for them the sum of Costs by Project ID is +3 -2 = +1, which is of course <> 0.

Thanks for any help in advance

Br

Kimmo

Project ID	Order ID	Posting Year	Costs
A	A1	2017	+5
A	A2	2018	-5
B	B1	2017	+3
B	B2	2018	-2

P.S. It's my first post and I'm not sure if this is a suitable Group where to post it. If there is a newbie forum somewhere for simple questions kindly let me know. Thanks.

[dwforest]

Maybe:

if(Sum(Aggr(Sum(Costs),[Project ID]))>0,Sum(Aggr(Sum(Costs),[Project ID])),null())

and then exclude nulls

[lblumenfeld · Accepted solution]

I verified that the formula below works. Use it as the dimension for Project ID and be sure to uncheck "Include null values".

=Aggr(If(Sum(Costs) > 0, Only([Project ID])), [Project ID])

(Note: If you also want to allow total project values less than zero, then change the ">" to "<>".

It will also ensure that the filter bar shows "Project ID" if you click on that column for selection.