Forum Discussion

marcos_osorio's avatar
marcos_osorio
Helper II
7 years ago
Solved

Problem with SUM

HI, i have a formula who works like this:

 

TH Total Unit Margin = CALCULATE(SUM('BI Analysis'[Amount])/SUM('BI Analysis'[Qtty]),YEAR('BI Analysis'[DATE RECORD])>2017,'Type of Data'[TYPE] = "Result")
 
The problem is that i have some products that makes SUM('BI Analysis'[Qtty]) zero. So if i filter for this products the value is inifinity.
How can I prevent to appear that value?
 
Thanks
 
 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Use the DIVIDE function, it accounts for a divide by zero error

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Use the DIVIDE function, it accounts for a divide by zero error

    • marcos_osorio's avatar
      marcos_osorio
      Helper II
      7 years ago

      Thank you very much!

      I have a lot to learn of Power BI.

      Thanks Again!

       

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi marcos_osorio,

     

    You can use an IF statement.

    TH Total Unit Margin =
IF (
    SUM ( 'BI Analysis'[Qtty] ) = 0,
    BLANK (),
    CALCULATE (
        SUM ( 'BI Analysis'[Amount] ) / SUM ( 'BI Analysis'[Qtty] ),
        YEAR ( 'BI Analysis'[DATE RECORD] ) > 2017,
        'Type of Data'[TYPE] = "Result"
    )
)


    Best regards,

    Yuliana Gu