Display 0 instead of blank value in matrix

[Anonymous]

Hello everyone !

I've checked this issue thoroughly online, and despite finding many leads that looked promising on this forum, none of them work for my specific dataset.

The dataset has the following structure:

What I want to do is to create a matrix that displays the revenue for each company, for each product category, with zero's instead of blank values if the company has no revenue for that product.

I've tried to create two measures:

Measure1 = IF(ISBLANK(CALCULATE(SUM(Sheet1[Revenue]))),0, CALCULATE(SUM(Sheet1[Revenue])))

Measure2 = CALCULATE(SUM(Sheet1[Revenue]))+0

However, none of them work as I keep seeing blank values instead of zeros...

This seems like it should be easy to do, but i've been stuck on this for a while now so your help would be very appreciated !

Thanks in advance !!

[v-xiaotang]

Hi @Anonymous 

Thanks for reaching out to us.

please try this

Measure1 = 
VAR _sum=CALCULATE(SUM('Table'[Column3]))
return
IF(ISBLANK(_sum),0, _sum)

Measure2 = 
VAR _sum=CALCULATE(SUM('Table'[Column3]))
return
_sum+0

Best Regards,

Community Support Team _Tang

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

[Anonymous]

Hi Xiaotang,

Thank you for your answer ! Unfortunately this is also not working, the issue is not that the value are null in my source data, but more that there are no rows for these combinations in the row data (ex: Comp2 made no revenue for product B)... 

[v-xiaotang]

Hi @Anonymous 

Thanks for reaching out to us.

please try this

Measure1 = 
VAR _sum=CALCULATE(SUM('Table'[Column3]))
return
IF(ISBLANK(_sum),0, _sum)

Measure2 = 
VAR _sum=CALCULATE(SUM('Table'[Column3]))
return
_sum+0

Best Regards,

Community Support Team _Tang

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

[Anonymous]

Hi Jorrafer25,

Thank you for the suggestion but unfortunately this is something I've already tried and it did not work (my measure 2 in the original post)...

[Jorrafer25]

Hello!

One option would be to create a measure like:

Revenue2=SUM(Sheet1[Revenue])+0

Best Regards