SUM if not blank

[jacek27031]

Hi, 

how to create a measure that  count only no-blank values? I have actual sales values and planned sales values. I need to show on the chart plan vs. actual values up to the present time.  

I've tried like this but there is some problem with the command ;/

=sum({<[# Sales Out Quantity]={"=len(trim([# Sales Out Quantity]))>0"} >} Sum([# Sales Out Quantity]) - sum([# SO Plan]))

[AshutoshBhumkar]

Hello,

You can create a Flag in script like below and use the same in Set Analysis.

Flag : If(Len(Trim([# Sales Out Quantity]))>0,1,0) as Flag.

Expression : Sum({<Flag = {'0'}>}Sales)

This will give you values for blank [# Sales Out Quantity]

Hope that helps.

Thanks,

Ashutosh

[jacek27031]

Thanks @AshutoshBhumkar 

Is there any other 1-step solution? I also need this measure on the KPI indicator.

And why did you use "Sales" in this code? Sum({<Flag = {'0'}>}Sales)

[AshutoshBhumkar]

Hello,

It was just an example. 🙂

You can add the same expression in KPI as well. 

Thanks,
Ashutosh

[MEDHA07]

If calculate only greather then zero values in measure

sum({<sales={">0"}>}sales)

Flag=0 ,means you need to create new filed with conditional statement if(isnull(filed) or filed=' ' or Filed='-',0,1) as Flag

sales i just used for example to show as measure