Divide function returning Postive %

[vjnvinod]

Hi,

i have encountered a strange situation, i have used this formula

Margin% = DIVIDE(Data[FYTD Margin],Data[FYTD ANSR])

my values are below, and its returning 270.9%, instead of -270.9%, is there any fix to it?

FYTD Margin

-203183.13

FYTD ANSR

-75144.86

[PhilipTreacy]

Hi @vjnvinod 

Great, glad to help.

Can you please mark my answer as the solution so we know this qs has been answered.

Thanks

Phil

[Singaravelu_R]

Hi @vjnvinod ,

Could you please try with if function as mentioned below. I think it gives the result which is the way you want. Please try and let us know

Column = if(DIVIDE('Table (5)'[FYTD Margin],'Table (5)'[FYTD ANSR],0)>0,DIVIDE('Table (5)'[FYTD Margin],'Table (5)'[FYTD ANSR],0)*(-1),DIVIDE('Table (5)'[FYTD Margin],'Table (5)'[FYTD ANSR],0)*(1))

[PhilipTreacy]

@vjnvinod 

If you require the result to always be positive wrap the calculation in ABS

Margin% = ABS(DIVIDE(Data[FYTD Margin],Data[FYTD ANSR]))

Phil

[vjnvinod]

@PhilipTreacy 

Actually not

if either of them is negative or both of them are negative, my value should show negative, thats the logic

is there any solution you can recommend?

[PhilipTreacy · Accepted solution]

Hi @vjnvinod 

Try this

Margin% = SWITCH( TRUE(),

    SIGN(Data[FYTD Margin]) = -1 || SIGN(Data[FYTD ANSR]) = -1 , ABS(DIVIDE(Data[FYTD Margin],Data[FYTD ANSR])) * -1,

    DIVIDE(Data[FYTD Margin],Data[FYTD ANSR])

)

Regards

Phil

[vjnvinod]

@PhilipTreacy

worked like a charm, awesome

[Fowmy]

@vjnvinod 

It is correct, when you divide minus value by another minus value, you get a positive answer.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[vjnvinod]

@Fowmy 

I understand that, but from a business perspective its not correct, i want to see my result in negative in those cases where one of them or both of them are negative

Hope i am making sense

[Fowmy]

@vjnvinod 

Try this measure and modify as per your variations:

Margin% = 
  var margin = Data[FYTD Margin]
  var ansr = Data[FYTD ANSR]
 return
 IF( Data[FYTD Margin] <0 || Data[FYTD ANSR] < 0,
     ABS(DIVIDE(Data[FYTD Margin],Data[FYTD ANSR])) * -1,
     DIVIDE(Data[FYTD Margin],Data[FYTD ANSR])
 )

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn