Sum total not equal to expected value

[davyqliks]

Hi All,

Looking at the below table, (Left) when i have the dimension Fibres my total is equal to 600 as expected.

(Right) When i remove the Fibres dimension and use set analysis, one of the values of 60 is not calculating giving me 60 short of the total expected. 

Sum( distinct {<SustainableLevel = {'Sustainable'}>}Weight)

Sum( distinct {<SustainableLevel = {'Not Sustainable'}>}Weight)

How can i get the total on the right table for the following:

Sum( distinct {<SustainableLevel = {'Sustainable'}>}Weight)

To include the other 60 to give me the 420.

I also tried an Aggr(Sum( distinct {<SustainableLevel = {'Sustainable'}>}Weight),Fibres) but i get a null return.

Thank you in advance.

Daniel

[brunobertels]

Hi 

you have 3 sustainable value 2*60 1* 300 

using distinct you will get 

1* 60

1* 300 

try using this 

Sum( {<SustainableLevel = {'Sustainable'}>}Weight)

[davyqliks]

Hi There,

Thank you for the reply, see and didnt consider this.

My issue is that when not using distinct the value is way out! this is a copy of the right table in the previous image:

Any ideas why?

i have no synthetic keys.

Thank you so much for the reply.

Daniel

[davyqliks]

HI All,

I just dont get this.

For a selected order i see here i have 3*130 and 1*260 weight.

Sum(Distinct Weight)  390 as expected

Sum(Weight) 21883680

See on the right table all data for this order... Can anyone shed some light on this?

Thank you, i'm losing my mind here 🙂

Kind regards,

Daniel

[brunobertels · Accepted solution]

Well not sure to be helpful in that case 

seems that you use distinct because sustainableLevel is repeated by Portal Order number 

try this 

Sum( distinct {<SustainableLevel = {'Sustainable'}>} total Weight)

or this 

Sum( total <Fibres> distinct {<SustainableLevel = {'Sustainable'}>}Weight)

or

 sum( total Aggr(Sum( distinct {<SustainableLevel = {'Sustainable'}>}Weight),Fibres) )

[davyqliks]

Absolute legend Bruno,

Thank you so much for sharing your time and expertise with me, option 3 was the one i was looking for,

Thank you

Daniel

[davyqliks]

HI Again Bruno,

I have one more slight issue with the given solution.

when i un filter, all are showing the same value

But when i make a selection the result is correct, different example below

Do you know why this is and how i can resolve? thanks and apologies for coming back to you on this.

Daniel

[brunobertels]

Hi 

Add dim Portal Order No in your agregation : 

may be this 

sum( total Aggr(Sum( distinct {<SustainableLevel = {'Sustainable'}>}Weight),Fibres, "Portal Order No" ) )

[davyqliks]

HI Bruno,

I did try this before coming back to you with the same result.

sum( total Aggr(Sum( distinct {<SustainableLevel = {'Sustainable'}>}Weight),Fibres, "Portal Order No." ) )

Any other ideas?

Thanks again.

Daniel

[brunobertels]

Nope 😞 

but this may be 

aggr(

sum( total Aggr(Sum( distinct {<SustainableLevel = {'Sustainable'}>}Weight),Fibres) )

, "Portal Order No." )

or 

Sum(

aggr(

sum( total Aggr(Sum( distinct {<SustainableLevel = {'Sustainable'}>}Weight),Fibres) )

, "Portal Order No." )

)