Help With If Sum measure for Pie chart Dimension

[davyqliks]

Hello again Experts.

I wonder if you can help.

I am using the following  if sum calculation to create a dimension in a pie chart/Table

=aggr(if(
Sum(
{<[We Care Materials] = {'Sustainable'}>}
([Garment average net weight (gram)] * [Weight Ratio] )/100 * Percentage / 100 ) / 2
/
[Garment average net weight (gram)] * 100
>=50,[We Care Fibre],'Not Sustainable',),[Portal Order No.])

If the result is >=50 i show the values in the we care fibre dimension, else name the others not sustainable.

This is working well however, i have just found a scenario where I have 2 We Care Fibre for a portal order no and this returns a - (null) for 200K.

the Measure used is:

Sum(Aggr(sum([Garment average net weight (gram)]) * QTY01,[Portal Order No.]))

Is there a way around this so i can show the 200k split Cotton (BCI - Better Cotton) and Cotton (Organic - GOTS)

Thank you so much in advance.

Daniel

[Or]

It looks like in both cases, you have a field operation that is outside of your sum()  - in the top formula this is [Garment average net weight (gram)]  and in the table this is QTY01. This means it is explicitly going to be replaced with Only(Field), and if you have two separate values, that won't work. You'll need to move those inside your sum or otherwise adjust your formula to handle this situation based on your business / data logic.

[davyqliks]

Thanks for the offering, 

i'm not quite sure how to achieve this.

I was wondering is it possible to assign the null as a value like 'Multi'?

I tried this:

if(isnull(Sum(Aggr(sum([Garment average net weight (gram)]) * QTY01,[Portal Order No.]))),'Multi',Sum(Aggr(sum([Garment average net weight (gram)]) * QTY01,[Portal Order No.])))

but i still get the - , is this because the value is not null?

Thanks again, if you can give any more on your original suggestion or a hint in the right direction that would be great. Thanks again.

Daniel