Forum Discussion

DiegoPerez85's avatar
DiegoPerez85
Frequent Visitor
9 years ago
Solved

Variation shows -100% for blank cells

Hi! I'm trying to show a table with past year and current year sales and the variation. For some brands where there is no data, the measure I created shows -100%. I would like to only see brands data, hiding the others.

 

This is the measure formula:

 

Var C9L vs LY = SUMX(Variables,Variables[Volume C9 Act (CY)])/SUMX(Variables,Variables[Volume C9 Act (LY)])-1

 

This is the result for blank data

 

 How can I solve this?

 

Thanks!!!

 

Diego

 

 

  • dedelman_clng's avatar
    dedelman_clng
    9 years ago

    You'll need to us IF logic to return a blank when there is a blank.  By subtracting 1, you're giving it a value.

     

    IF  (SUMX( Variables,Variables[Volume C9 Act (CY)] ) <> 0,
 SUMX(Variables,Variables[Volume C9 Act (CY)])/SUMX(Variables,Variables[Volume C9 Act (LY)])-1,
 BLANK())

     

    I would also use DIVIDE instead of the tranditional A/B

     

    Hope this helps

    David

  • DiegoPerez85's avatar
    DiegoPerez85
    9 years ago

    Hi! Thanks for your help! I improved the formula and now it's working perfectly. This is how it ended:

     

    Var C9L vs LY = IF(DIVIDE(SUMX(Variables,Variables[Volume C9 Act (CY)]),SUMX(Variables,Variables[Volume C9 Act (LY)])) <> BLANK(),(DIVIDE(SUMX(Variables,Variables[Volume C9 Act (CY)]),SUMX(Variables,Variables[Volume C9 Act (LY)]))-1),BLANK())

     

    Thanks again!

     

    Diego