DAX function not calculating decimal values correctly.

[abansalgeoforce]

I have a simple DAX function and have realized that powerBi doesn't calculate the values correctly. It rounds off to nearest integer. i have tried multiple suggestions (as mentioned below), but none of them work.

Original calculation. - 

score_custom =

VAR var1 = table[column1] 

RETURN

    SWITCH(

        TRUE(),

        ISBLANK(var1), 100,

       var1 >= 0 &&var1 <= 1, 100 + ((var1)  / (1 - 0) * (-10)),

       var1 > 1 &&var1 <= 5, 90 + ((var1 - 1) / (5 - 1) * (80 - 90)),

       var1 > 5 &&var1 <= 10, 80 +  ((var1 - 5) / 5 * (-10)),

       var1 > 10 &&var1 <= 15, 70 + ((var1 - 10) / (15 - 10) * (60 - 70)),

        60

    ) 

Now, in my case, var1 = 6.66.

So, calculation I am expecting is -
 var1 > 5 &&var1 <= 10, 80 +  ((var1 - 5) / 5 * (-10)),

answer should be = 76.68. However, the answer is gives me is 70.00

i tried to see where the calculation is breaking, and if i use the below simpler calculation

"var1 > 5 &&var1 <= 10, (var1 - 5) / 5" , i am expecting a value of 0.33, however, I get a value of 0.4 (rounding off).

i have tried -

1) in the variable, force it to be a decimal - VAR var1 = table[column1] * 1.00 (or even a round funxction)

2) Apply round within the calculation.

but none of them give me the right calculation.

[abansalgeoforce]

Thanks for you response. However, even with DIVIDE function i get the same values (0.40 instead of 0.366)

[v-vpabbu]

Hi @abansalgeoforce,

This is definitely a formatting issue, not a DAX calculation issue.
To resolve this:  Go to Format, increase Decimal places from 2 to at least 3 or more.

Regards,

Vinay Pabbu

[abansalgeoforce]

column1 here is set to decimal with 3 precision.

And the below calculation is giving me value 0.40 instead of 0.33

"var1 > 5 &&var1 <= 10, (var1 - 5) / 5" , 

var1= 6.66

(6.66-5)/5 = 1.66/5 = 0.332.

Even if its rounding to 2 decimals, i should get value as 0.33. However, the value i finally get is 0.40.

The final measure (for which DAX formula i provided above) is set to decimal,2. However, with the interim calculation not being correct, the value is 0.40 instead of 0.33.

[v-vpabbu]

Hi @abansalgeoforce,

The issue could be caused by implicit type conversion or a column misreference in your DAX logic.
Try with simplified test measure to isolate the issue

If you still see 0.40 instead of 0.332: It's likely that column1 is being rounded earlier, possibly in Power Query.

Regards,

Vinay Pabbu

[abansalgeoforce]

Thanks @v-vpabbu . I tried exactly the same. When I use just column1, I see value 6.66. When I do column1-1, I get 1.66. The moment I do (column1-1)/5, I get 0.4

[v-vpabbu · Accepted solution]

@abansalgeoforce, Looks like Power BI is auto-converting the result to a fixed decimal with low precision at some point in the DAX chain.

[v-vpabbu]

Hi @abansalgeoforce,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @abansalgeoforce,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @abansalgeoforce,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[bhanu_gautam]

@abansalgeoforce  You can force the calculation to be treated as a decimal by explicitly converting the values to decimals.

DAX
score_custom =
VAR var1 = table[column1] * 1.0

RETURN
SWITCH(
TRUE(),
ISBLANK(var1), 100,
var1 >= 0 && var1 <= 1, 100 + ((var1) / 1.0 * (-10)),
var1 > 1 && var1 <= 5, 90 + ((var1 - 1) / 4.0 * (80 - 90)),
var1 > 5 && var1 <= 10, 80 + ((var1 - 5) / 5.0 * (-10)),
var1 > 10 && var1 <= 15, 70 + ((var1 - 10) / 5.0 * (60 - 70)),
60
)

Instead of direct division, you can use the DIVIDE function to handle division operations and ensure decimal precision. 

DAX
score_custom =
VAR var1 = table[column1]

RETURN
SWITCH(
TRUE(),
ISBLANK(var1), 100,
var1 >= 0 && var1 <= 1, 100 + DIVIDE(var1, 1.0) * (-10),
var1 > 1 && var1 <= 5, 90 + DIVIDE(var1 - 1, 4.0) * (80 - 90),
var1 > 5 && var1 <= 10, 80 + DIVIDE(var1 - 5, 5.0) * (-10),
var1 > 10 && var1 <= 15, 70 + DIVIDE(var1 - 10, 5.0) * (60 - 70),
60
)