sum over all records not working

[TomBond77]

Hi experts

I have the following formula, a master item measure:

if(title<>'Service',
if([Cap_D_W]='5' , 1 * sum([Cap_H_D]), 0.0), 0.0)

On row level you can see the correct values, on top/Total level there is a 0.0 as result which is incorrect.

[Cap_H_D] is a dimension with two possible values (8 or 24, as hours per day).

Where is the problem, why is there no result on top level / Total?

[Or · Accepted solution]

An If() statement placed outside of an aggregation function such as sum() will be evaluated for a single value, equivalent to using Only(). This means that multiple values for these fields will result in null. You should probably re-write the expression moving the if() statements inside the sum() statement.