Show Zero in table

[DuncanYeah]

Hi everyone,

I am trying to get my table to display categories with number count zero.
I had tried to use +0 but after appling page filter, the table showed some categories that not match the page filter condition.

Anyone know anyways to solve this problem.

Thank You.

Best Regards,

Duncan

[Ritaf1983]

Hi @DuncanYeah 
It is highly likely that a manipulation within the DAX formula can be implemented to preserve the filters as well.
To provide further assistance, access to the data and a clear understanding of the desired outcome are necessary.

you please provide some raw data in your tables (exclude sensitive data) with Text (not a screenshot) format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

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

[DuncanYeah]

Hi @Ritaf1983,

Thx for your reply, I had created a pbix file simulated my situation.

https://drive.google.com/file/d/1Jn17RmjsW1DyiMbeNwsIZ8k9gAw00Fad/view?usp=sharing  

And I cannot do the expected result  in the desktop so i provide it here:

P_Cat	Sum_income
A	100
B	0

Thank you.

Best Regards,

Duncan

[Ritaf1983]

Hi @DuncanYeah 
Try this formula :

Sum_income =

var tab = SUMMARIZE(I_table,I_table[Project type],"sum_",sum('I_table'[Income]))

return

SUMX(tab,[sum_])

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

[DuncanYeah]

Hi @Ritaf1983 ,

Thx for your reply and it kinda works.

However, after applying other filters the categories just disappear instead of showing zero.

Thank You.

Regards,

Duncan

[Ritaf1983]

Hi @DuncanYeah 
You need to create a model + formulas that serve your scenario...

[DuncanYeah]

Hi @Ritaf1983 ,

Sorry for missing the filter in the past example.

I recreate the pbix that have a filter inside.

https://drive.google.com/file/d/1Swv7apy3WZ9KXVwVEUdV8SJGhfLtILbo/view?usp=drive_link 

Sorry and thank you for your reply and effort.

Regards,

Duncan

[Ritaf1983]

Now i don't understand what a desired result is...+
You add a row with a blank value...is this a value that should be 0?

[DuncanYeah]

Yes the value should be zero.

The desired result is replace blank value with zero without adding other categories to the table, which is not belong to the project type. 

Regards,

Duncan

[Ritaf1983]

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

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

[DuncanYeah]

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

[Ritaf1983]

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

[DuncanYeah]

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

[Anonymous]

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.

[DuncanYeah · Accepted solution]

Hi @Anonymous 

Thx for your reply.

I change the code a little bit and it works.

Code:

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,x+0
)

 Thank You.

Best Regards,

Duncan