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

[davyqliks]

Really Appreciating your time Bruno,

but so sorry to say both of these have the same return as before.

Thank you

Daniel

[davyqliks]

Hi,

There is one difference,

aggr(

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

, "Portal Order No." )

gives the total of the measure as 2448

while 

this

Sum(

aggr(

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

, "Portal Order No." )

) 

totals the rows to 14668

Thank you Bruno. sorry this is not so simple to get the desired result.... you're still a legend for sharing your time and expert help.

thank you

Daniel

[davyqliks]

Hi Bruno,

Did you have any other ideas on why the totals are not showing the values when selected individually?

Thank you

Daniel