Forum Discussion

abansalgeoforce's avatar
abansalgeoforce
Frequent Visitor
1 year ago
Solved

DAX function not calculating decimal values correctly.

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.
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

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

13 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 months ago

      Did not solve the issue with the same problem Im having similar to the original question. 

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        9 months ago

        Hi,

        Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

  • abansalgeoforce's avatar
    abansalgeoforce
    Frequent Visitor
    1 year ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        abansalgeoforce
        Frequent Visitor
        1 year ago

        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.