Expression to show only aggregated 0 values

[regowins]

Hi All,

Here is my current expression which works as needed. However, I am wondering if there is a more efficient way to do this.

If (Aggr(Sum({<[Stock]={'In stock','out-stock'}>}[Allocated ]),[Material],[Plant ]) = 0
and Aggr(Sum({<[Stock]={'In stock','out-stock'},>}[Surplus ]),[Material ],[Plant ]) <> 0,
Aggr(Sum({<[Stock]={'In stock','out-stock'}>}[Direct Cost]),[Material ],[Plant]))

I only want to see the sum of direct cost for the aggregated material plant combination if the Sum of Allocated Qty is 0 and the sum of surplus Qty is <> 0 for the aggregated material plant combination for Stock & out-Stock.  In the example data I should only see a single line for material ABC-044-651 and direct cost of 17

Thanks for the help

Material	Plant	Stock	Direct Cost	Allocated	Surplus
XXX-015-763	3999	stock	0	0	0
XXX-015-763	3999	stock	4	0	13
XXX-015-763	3999	stock	7	0	25
XXX-015-763	3999	stock	8	0	26
XXX-015-763	3999	out-stck	9	10	25
ABC-044-651	3003	out-stck	0	0	0
ABC-044-651	3003	out-stck	5	0	1
ABC-044-651	3003	out-stck	10	0	1
ABC-044-651	3003	stock	2	0	3
123-044-652	3980	stock	9	0	4
123-044-652	3980	stock	2	3	0

[JonnyPoole · Accepted solution]

you can avoid the aggr() if using the expression in a chart with both Plant and Material as dimensions:

if(

  sum( total <Plant,Material> Allocated) = 0

  and

  sum( total <Plant,Material> Surplus) <> 0,

  sum(total <Plant,Material> [Direct Cost])

)

[regowins]

Great Thanks!  I knew there was another way of doing this. Unfortunately, I have a small discrepancy between the two expressions and need to figure out which is more accurate  .