Forum Discussion

PBI_newuser's avatar
PBI_newuser
Post Prodigy
4 years ago
Solved

How to replace empty/blank cell to zero?

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]
 )))

 

 

 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

3 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    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's avatar
    YukiK
    Impactful Individual
    4 years ago

    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!