Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX formula calculation gives wrong value!?

Hello,

I run into a dilemma with a calculation in DAX that works fine for one column and gives wrong results if applied to a different column.

I have this data set , where STC, STCR, LTC and LTCR are measures. STC, STCR, LTC numbers are correct based on the formula applied. Strangely, the LTCR gives wrong results (close but not right).

Here are the measures used:

STC = ([SecondLatestValue] - [LastValue]) * 1.5
STCR = DIVIDE([LastValue] - VALUES('Piping UT ex'[Tmin (mm)]), [STC])
LTC = ([FirstValue] - [LastValue]) * 1.33
LTCR = DIVIDE([FirstValue] - VALUES('Piping UT ex'[Tmin (mm)]), [LTC]).
All formulas give the right result but the LTCR! For first row it should be 19.79, not 19.84. Same with the rest of the records for LTCR. Does anybody encountered this problem?

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    There seems nothing error with the measure.

     

    LTCR = DIVIDE([FirstValue] - MIN('Piping UT ex'[Tmin (mm)]), [LTC])

     

     

    We can calculate the ([FirstValue] - MIN('Piping UT ex'[Tmin (mm)]) first.

     

    This part calculates well.

     

     

    Let me check the measure [LTC].

     

     

    LTC = ([FirstValue] - [LastValue]) * 1.33

     

     

    First LTC result is (12.9-12.54)=0.36. 0.36*1.33=0.4788.

     

    Then 9.5/0.4788=19.84.

     

    If you want the reslut  19.84 change to be 19.79. Please try to change the measure [LTC].

     

     

    LTC = var _1= ([FirstValue] - [LastValue]) * 1.33 return ROUND(_1,2)

     

     

     

     

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

20 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    There seems nothing error with the measure.

     

    LTCR = DIVIDE([FirstValue] - MIN('Piping UT ex'[Tmin (mm)]), [LTC])

     

     

    We can calculate the ([FirstValue] - MIN('Piping UT ex'[Tmin (mm)]) first.

     

    This part calculates well.

     

     

    Let me check the measure [LTC].

     

     

    LTC = ([FirstValue] - [LastValue]) * 1.33

     

     

    First LTC result is (12.9-12.54)=0.36. 0.36*1.33=0.4788.

     

    Then 9.5/0.4788=19.84.

     

    If you want the reslut  19.84 change to be 19.79. Please try to change the measure [LTC].

     

     

    LTC = var _1= ([FirstValue] - [LastValue]) * 1.33 return ROUND(_1,2)

     

     

     

     

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you, probably my test table had it set up with the right decimals and here was not rounded. Much appreciate all those who responded, KNP , katika555 , Anonymous. Lesson learned for the future. Question: Does it matter if I use VALUES instead of MIN, what would be the disadvantage?
       
       
      • KNP's avatar
        KNP
        Super User
        3 years ago

        You're welcome.

        VALUES returns a table of distinct values.

        MIN returns a scalar value.

        VALUES will likely fail if you put the measue in a different visualisation.

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Tmin is a column, not a measure, and if you don't use values, it gives an error.

       

  • KNP's avatar
    KNP
    Super User
    3 years ago

    Where does 'Piping UT ex'[Tmin (mm)] column come from? A seperate table?

    If yes, would be good to include a screenshot of your data model so we can understand the relationships.

    If it's the same table then katika555 answer should solve the issue for you.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      It is one simple table, no relationships involved

       

      • KNP's avatar
        KNP
        Super User
        3 years ago

        Ok, always best to post this detail at the begining. 

        Try to include sample data as data (not screenshot) as it makes it easier to provide an answer.

         

        Depending on where else you use the measure, this may give you what you want.

        LTCR =
DIVIDE (
    [FirstValue]
        - MIN ( 'Piping UT ex'[Tmin (mm)] ),
    [LTC]
)

         

        In the row context of that table, this will work fine.

         

  • katika555's avatar
    katika555
    Resolver I
    3 years ago

    If our dataset is like this picture, you dont need any Values just Divide, or share your file.

    • KNP's avatar
      KNP
      Super User
      3 years ago

      Yeah, but Anonymous is trying to add a measure, not a column.