Converting Excel Formula to Power BI

[Anonymous]

Hi,

I'm trying to replicate this formula (a calculated field within a Pivot table):

=IFERROR(IF(AND('Sales'=0,'Forecast'>0),0, 1-IF('ABS Error'/'Sales'>1,1,'ABS Error'/'Sales')),NA())

This is what I did (calculated column):

=IFERROR(IF(AND([Sales]=0,[Forecast]>0),0, 1-IF([ABS Error]/[Sales]>1,1,[ABS Error]/[Sales])),BLANK())

But I'm getting different figures for example the excel formula will show 63.8% but Power BI shows 629.8%

Any ideas what I might be doing wrong or how to write it better?

[Anonymous · Accepted solution]

@Anonymous Hey ,
Can tell your  requirement .what exactly are trying to achieve from this .

you can try this as a measure .

=IFERROR(IF(AND([Sales]=0,[Forecast]>0),1, 1-IF([ABS Error]/[Sales]>1,1,[ABS Error]/[Sales])),BLANK())

or 

=IFERROR(IF(AND([Sales]=0,[Forecast]>0),1-IF([ABS Error]/[Sales]>1,1,[ABS Error]/[Sales])),BLANK())

Try above formula

[Anonymous]

Thank you, the first measure worked, I also needed to create sum measures of the columns used.