How to replace empty/blank cell to zero?

[PBI_newuser]

Hi, I have a measure to calculate the Volume Impact. But some of the cell in [Volume Sum] is blank. 

If the [Volume Sum] is blank, I want it to be set as zero so that it could calculate the volume impact measure correctly.

For example, Product A in 2020-10, the [Volume Impact Total] should be (0-1)*313 = -313 [395 which is incorrect].

For Product B in 2021-09, the [Volume Impact Total] should calculate as (0-1)*34 = -34.

The [Volume Impact Total] should has the same value as [Revenue YoY] for those lines highlighted in red.

How to fix the measure below? Here is the pbix file.

Volume Impact = 
IF (
MIN ( 'Calendar'[Date] ) > MAX ( 'Append Table'[Date] ),
CALCULATE (
SUMX (
SUMMARIZE ( 'Append Table', 'Calendar'[YearMonth],'Append Table'[Material_Combine]),
IF (
[Volume Last Year] <= 0,
 ( [Revenue] - [Revenue Last Year] ),
 ( [Volume Sum] - [Volume Last Year] ) * [AUSP Last Year]
 )),
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
 )*-1,
SUMX (
SUMMARIZE ( 'Append Table','Calendar'[YearMonth], 'Append Table'[Material_Combine] ),
IF (
[Volume Last Year] <= 0,
 ( [Revenue] - [Revenue Last Year] ),
 ( [Volume Sum] - [Volume Last Year] ) * [AUSP Last Year]
 )))

[v-xiaotang · Accepted solution]

Hi @PBI_newuser 

I tried, change the blank cell to zero won't help,

But values become correct when I try this,

So you may need to check the code in variable _a.

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.

[YukiK]

There are multiple ways to do this, but one way I'd usually go with is to add "+ 0" to your measure. e.g. If I have a measure saying 

SUM(sales)

then I'll change it to

SUM(sales) + 0

Please give it a thumbs up if this helps!

[PBI_newuser]

Hi @YukiK , thanks for the suggestion but it doesn't work for my case.