Sum fields if multiple criteria are met

[hellyars]

Reference the sample dataset below.  Basically I need to sum A-B or A-(B+E) for all ITEMS that have either a B, E, or B and E cost type. 

IF ITEM is the same, and cost type B is present (but not E), subtract the sum of B from A

IF ITEM is the same, and both cost B and E are present, subtract the sum of B+E from A.

I know that I can use a Multi Row to subtract B from A if B exists.  I assume I can do the same for B and E and then subtract from A. But, I am hoping there is simpler way of doing it. 

ITEM	COST TYPE	Value
Trucks	A	341
Planes	A	2355
Planes	C	3252
Boats	A	13525
Boats	B	-1345
Boats	E	-1344
Boats	L	3425
Rockets	A	2355
Rockets	B	-1345

[Thableaus · Accepted solution]

Hi @hellyars 

Does that suit you? It's a different approach, I don't know if it's good for you.

Cheers,

[Thableaus]

@hellyars 

If you'd like to keep it in rows, you could tranpose it back, using transpose tool.

Cheers,

[hellyars]

Looks interesting.  I am going to test it with the real data, which is a bit messier (with multiple value years).  I will get back to you.

[hellyars]

It works - but a lot of flipping.  Get's a little busy when you are dealing with multiple value years (e.g., call value 2016, the add similar columns for 2017 and 2018).  

Thanks

[Thableaus]

@hellyars 

No problem. It's just a quick idea, but of course depending on data structure and size, some changes and adaptions are needed.

No model is perfect all the time.

Cheers,