I need help converting a simple Excel formula to DAX

[Anonymous]

I am currently struggling converting an excel formula to DAX format. The formula is:

=IFERROR(IF([Variance]/ABS([2021 Amount])<-10000000,-1,IF([Variance]/ABS([2021 Amount])>10000000,1,[Variance]/ABS([2021 Amount]))),0)

Variance and 2021 Amount are both the collumns I am calculating the data from. I am adding it as a measure in a pivot table because when I filter the pivot table I want the percentage of varance be calculated on the grand total as well rather than a sum of the percentages. Which is useless when looking at percentages.

Many thanks.

[Anonymous]

HI @Anonymous,

Any update on this? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[vapid128]

In dax, the formula should be all same.

All you need to do is repleace A/B to DIVIDE(A,B) 

[Anonymous]

Thank you for your contribution vapid128. I replaced the formula with the following but unfortunately it did not work.

IFERROR(IF(DIVIDE([Variance],ABS([2021 Amount]))<-10000000,-1,IF(DIVIDE([Variance],ABS([2021 Amount]))>10000000,1,DIVIDE([Variance],ABS([2021 Amount])))),0)

It is giving me the following message. 

"A single value for column 'Variance' in table 'Range' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'."

[vapid128]

I am sorry, I didn't see you want to add a measure.

Since [Variance] is a colnum, you need to tell dax, how it become a measure.

like Min/Max/Sum/Average.....

When you write "[Variance]/ABS([2021 Amount])<-10000000"

IF all in colnum or measure, it will be very clear to understand.

But if it is from colnum to measure, it is not that clear.