Dax formula question

[BORUCH]

Hi all

i have been breaking my head to figure this out if someone can help me i would greatly appreciate it

the formula is as follows

=IF(data[Discount Percentge]=0,"no discount",IF(AND(data[Discount Percentge]>0,data[Discount Percentge]<=0.05),"discount 1 .00% to 5.00%",IF(AND(data[Discount Percentge]>=0.05000001,data[Discount Percentge]<=0.1),"discount 2 5.00% to 10.00%",IF(AND(data[Discount Percentge]>=0.10001,data[Discount Percentge]<=0.35),"Discount 3 10.00 % TO 35.00 %",IF(AND(data[Discount Percentge]>=0.35001,data[Discount Percentge]<=0.75),"Discount 4 35.00 % TO 75.00 %",IF(AND(data[Discount Percentge]>=0.75001,data[Discount Percentge]<=100),"DISCOUNT 5 75.00 % TO 100.00 %","NEGATIVE"))))))

my problem is if i have a discount percent of exactly 5.00% it shows negative the question is why

if someone can please help me

thanks

 

[BORUCH]

HI

anyone has an answer ?

 

[ExcelingInLife]

You should consider using trunc(data[Discount Percentge],5) or whatever number, you are likely getting caught in decimals

 

[JustynaMK]

Hi,

I agree with the previous post, it might be the issue with decimal places. Try replacing "NEGATIVE" with FORMAT(DP, "0.00000") to display a value with 5 decimal places in order to see the true amount. Increase the number of decimal places if this still didn't give you an answer.

On a side note, I recommend getting familiar with SWITCH function - it will really make your life easier!

var DP = data[Discount Percentge]
return
SWITCH(
    TRUE(),
    DP=0, "no discount",
    AND(DP>0,DP<=0.05), "discount 1 .00% to 5.00%",
    AND(DP>0.05,DP<=0.1), "discount 2 5.00% to 10.00%",
    AND(DP>0.1,DP<=0.35), "Discount 3 10.00 % TO 35.00 %",
    AND(DP>0.35,DP<=0.75), "Discount 4 35.00 % TO 75.00 %",
    AND(DP>0.75,DP<=1), "DISCOUNT 5 75.00 % TO 100.00 %",
    "NEGATIVE"
)

 

[BORUCH]

Hi,

I agree with the previous post, it might be the issue with decimal places. Try replacing "NEGATIVE" with FORMAT(DP, "0.00000") to display a value with 5 decimal places in order to see the true amount. Increase the number of decimal places if this still didn't give you an answer.

On a side note, I recommend getting familiar with SWITCH function - it will really make your life easier!

var DP = data[Discount Percentge]
return
SWITCH(
    TRUE(),
    DP=0, "no discount",
    AND(DP>0,DP<=0.05), "discount 1 .00% to 5.00%",
    AND(DP>0.05,DP<=0.1), "discount 2 5.00% to 10.00%",
    AND(DP>0.1,DP<=0.35), "Discount 3 10.00 % TO 35.00 %",
    AND(DP>0.35,DP<=0.75), "Discount 4 35.00 % TO 75.00 %",
    AND(DP>0.75,DP<=1), "DISCOUNT 5 75.00 % TO 100.00 %",
    "NEGATIVE"
)

hi

thanks for the formula

can you amend the formula that if the discount percentage is 5.000000000000000%

it would still show up in the first discount group and the same for 10 and 35 etc..

 

[JustynaMK]

Hi,

If the discount percentage is equal to 5.000000000000000% then it should, in theory, appear in the first group -

AND(DP>0,DP<=0.05), "discount 1 .00% to 5.00%"

Just to be sure that we are working with the correct values, it might be worth adding ROUND function. See the first line -

var DP = ROUND(data[Discount Percentge], 2)
return
SWITCH(
    TRUE(),
    DP=0, "no discount",
    AND(DP>0,DP<=0.05), "discount 1 .00% to 5.00%",
    AND(DP>0.05,DP<=0.1), "discount 2 5.00% to 10.00%",
    AND(DP>0.1,DP<=0.35), "Discount 3 10.00 % TO 35.00 %",
    AND(DP>0.35,DP<=0.75), "Discount 4 35.00 % TO 75.00 %",
    AND(DP>0.75,DP<=1), "DISCOUNT 5 75.00 % TO 100.00 %",
    "NEGATIVE"
)

If it still doesn't do the trick, can you paste some screenshots and/or exemplary data?

 

[BORUCH]

Hi,

If the discount percentage is equal to 5.000000000000000% then it should, in theory, appear in the first group -

AND(DP>0,DP<=0.05), "discount 1 .00% to 5.00%"

Just to be sure that we are working with the correct values, it might be worth adding ROUND function. See the first line -

var DP = ROUND(data[Discount Percentge], 2)
return
SWITCH(
    TRUE(),
    DP=0, "no discount",
    AND(DP>0,DP<=0.05), "discount 1 .00% to 5.00%",
    AND(DP>0.05,DP<=0.1), "discount 2 5.00% to 10.00%",
    AND(DP>0.1,DP<=0.35), "Discount 3 10.00 % TO 35.00 %",
    AND(DP>0.35,DP<=0.75), "Discount 4 35.00 % TO 75.00 %",
    AND(DP>0.75,DP<=1), "DISCOUNT 5 75.00 % TO 100.00 %",
    "NEGATIVE"
)

If it still doesn't do the trick, can you paste some screenshots and/or exemplary data?

Hi

Thanks again for helping me

I'm getting an error as follows.

"Calculation error in column 'data'[]: An argument of function 'ROUND' has the wrong data type or the result is too large or too small."

I just want to be more clear what my goal is here in order for it to say "discount 1 .00% to 5.00%" . it needs to be less then and including the 5 percent even 5.000000000000%

the same is with 10 percent and 35 percent it needs ti include the 5 percent in the discount 1 right now with the first formula you provided me if you expand the percentage it only includes up to 4.999999999 etc.

thanks again for your help its greatly appreciated

 

[BORUCH]

OK i figured it out

I just added the if error and increased the decimal place on round to 22

thanks

 

[JustynaMK]

Ahh nice! Glad you figured it out