Power Pivot IF and Statement Help

[sjuhl99]

HI All,
I am trying to create a calculated column using an if/and DAX statement in PowerPivot and for some reason I keep getting errors so I am not sure if its the formula or if I am completely unable to perform what I want. My formula is below, can someone please help

=IF(([Actual Order Units]=0 && [Forecast Units (30)]>0), "No DMD W/FCST" , ([Actual Order Units]/[Forecast Units (30)]))

 

[arunsjain]

Hi,

Try this

=IF(AND([Actual Order Units]=0, [Forecast Units (30)]>0), "No DMD W/FCST" , ([Actual Order Units]/[Forecast Units (30)]))

 

[arunsjain]

If you can replace "No DMD W/FCST" with 0 then both If statements will work.

 

[arunsjain]

Or Other way is

=IF(([Actual Order Units]=0 && [Forecast Units (30)]>0), "No DMD W/FCST" , Format(([Actual Order Units]/[Forecast Units (30)]),0)

 

[sjuhl99]

Thank you for your help, unfortunately I am getting the same error so not sure what I want to do is even possible. Essentially I want to call out where the #DIV/0 error occurs with Text that is directional in nature.

 

[CALUCHO]

Greetings, very attention to your comments and thank you but how would you do to make a calculator with the percentages included in requesting the major amount, who could help me.

 

[sjuhl99]

I am sorry, I don't understand your question

 

[arunsjain]

Thank you for your help, unfortunately I am getting the same error so not sure what I want to do is even possible. Essentially I want to call out where the #DIV/0 error occurs with Text that is directional in nature.

Try this


=IF(([Actual Order Units]=0 && [Forecast Units (30)]>0), "No DMD W/FCST" , Format(iferror([Actual Order Units]/[Forecast Units (30)],"TEXT"),0)


Replace "TEXT" with your desired text.