Problem with SUM

[marcos_osorio]

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

[v-yulgu-msft]

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

[Anonymous · Accepted solution]

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

[marcos_osorio]

Thank you very much!

I have a lot to learn of Power BI.

Thanks Again!