Forum Discussion
Report Builder - Need IFERROR Equivalent
Using MS Report Builder, I have a calculated field in the report where part of the equation is rendering a 0 and as a result the whole calculation is getting set to 0. However, I want to include an "IFERROR" (I'm not sure what the equivalent is in PBI Report Builder) to each part of the equation:
Original Formula:
=(Sum(Fields!PrevBal_x_PrevFtpRate_Step1_3.Value)/Sum(Fields!PrevBook_Step1_3.Value)) - (Sum(Fields!PrevBal_x_PrevFtpRate_Step1_2.Value)/Sum(Fields!PrevBook_Step1_2.Value))
Proposed Solution:
=IFERROR(Sum(Fields!PrevBal_x_PrevFtpRate_Step1_3.Value)/Sum(Fields!PrevBook_Step1_3.Value),0) - IFERROR(Sum(Fields!PrevBal_x_PrevFtpRate_Step1_2.Value)/Sum(Fields!PrevBook_Step1_2.Value),0)
4 Replies
The built in DIVIDE function has the option to specify an alternative result (i.e what happens if there is a 0 in the denominator) - https://learn.microsoft.com/en-us/dax/divide-function-dax. Basically, you would be able to put 0 as the 3rd parameter
vicky_ I can't get it to work. This is supposed to work in the PowerBI MS Report Builder?
I've also tried using the following calculation but it's not working either:
=IIf(IsNothing(Sum(Fields!PrevBal_x_PrevFtpRate_Step1_3.Value)/Sum(Fields!PrevBook_Step1_3.Value))=0,0,Sum(Fields!PrevBal_x_PrevFtpRate_Step1_3.Value)/Sum(Fields!PrevBook_Step1_3.Value))-IIf(IsNothing(Sum(Fields!PrevBal_x_PrevFtpRate_Step1_2.Value)/Sum(Fields!PrevBook_Step1_2.Value))=0,0,Sum(Fields!PrevBal_x_PrevFtpRate_Step1_2.Value)/Sum(Fields!PrevBook_Step1_2.Value))
Maybe you can try this:
=IIf(IsNothing(Sum(Fields!PrevBook_Step1_3.Value)) Or Sum(Fields!PrevBook_Step1_3.Value) = 0, 0, Sum(Fields!PrevBal_x_PrevFtpRate_Step1_3.Value)/Sum(Fields!PrevBook_Step1_3.Value)) - IIf(IsNothing(Sum(Fields!PrevBook_Step1_2.Value)) Or Sum(Fields!PrevBook_Step1_2.Value) = 0, 0, Sum(Fields!PrevBal_x_PrevFtpRate_Step1_2.Value)/Sum(Fields!PrevBook_Step1_2.Value))
Best Regards,
Jing
