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...
  • 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

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
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
Frequent Visitor
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