Help with Sum expression required (NULL)

[davyqliks]

Hi,

I have the following Measure which works correctly on line detail but the total is null value -

Sum([UK Invoice Item Unit Price] - [UK Item Master Rebate Unit Price]) * [Stock Estimated Balance UK]

I have to show the total of this single measure with no dimensions but due to nulls in the data the total is null.

Please can anyone write to the expression below to exclude null

Sum([UK Invoice Item Unit Price] - [UK Item Master Rebate Unit Price]) * [Stock Estimated Balance UK]

or am i on the wrong path here?

with dimension in table:

with dimension removed

thank you for any help in advance.

Daniel

[Or · Accepted solution]

Presumably each line has its own Stock Estimated Balance UK, so when you reach the total line you can't multiply by this value. Without knowing what your expected result is, it's not possible to re-write the formula, though - you'll probably have to aggregate the field in question some way to get a result. For example, this method will aggregate at the item level and then sum that up (I think - can't test it):

sum(aggr(sum(Sum([UK Invoice Item Unit Price] - [UK Item Master Rebate Unit Price]) * Only([Stock Estimated Balance UK]),[Item No.]))

[davyqliks]

Hi @Or 

Just wanted to say thanks for the advice, this helped me get the desired results!

Thanks 

Daniel