Show Zero in table

DuncanYeah 
You can replace the blank value with zero from PQ :

Hi Ritaf1983 ,

Thank you for your reply.

I can do that in my dummy data but in real situation I am not allow to change the data from PQ.

Regards,

Duncan

Hi DuncanYeah 
You can use this DAX :

Sum_income =

var

t1 =SUMMARIZE( CALCULATETABLE('I_table',I_table[Income]<> BLANK()),I_table[I_NO],I_table[B_type],I_table[Project type],"sum1",SUM(I_table[Income]))

var

 t2 =SUMMARIZE( CALCULATETABLE('I_table',I_table[Income] = BLANK()),I_table[I_NO],I_table[B_type],I_table[Project type],"sum2",SUM(I_table[Income])+0)

 RETURN

SUMX(t1,[sum1])+SUMX(t2,[sum2])

The updated pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Hi Ritaf1983 ,

Thx for reply.

It works if the filter parameters are all in the same table.However, in the real case the filters parameter are from three different table.

Thank you for your afford and patient.

Regards,

Duncan

Hi DuncanYeah ,

You can try this code to create a measure.

Sum_income = 
var x = SUM(I_table[Income])
var _P_CatList = CALCULATETABLE(VALUES(P_table[P_Cat]),FILTER(I_table,I_table[Project type] IN VALUES(I_table[Project type])))
return 

IF(MAX(P_table[P_Cat]) in _P_CatList,x+0,x)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.