DAX formula calculation gives wrong value!?

[Anonymous]

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?

[v-rongtiep-msft · Accepted solution]

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]

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 , @v-rongtiep-msft. Lesson learned for the future. Question: Does it matter if I use VALUES instead of MIN, what would be the disadvantage?

[KNP]

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]

@KNP Thank you.

[katika555]

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

[KNP]

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

[katika555]

Same with measure 🙂

[KNP]

Yep, except I think something is going on with the data because the measure is simple.

@Anonymous - post data, PBIX and even code from advanced editor could help. Hide any sensitive info. Something is going on in the data/calculations for you to think it is incorrect. We can't answer without further info.

[Anonymous]

Hope the link works:

https://1drv.ms/u/s!Aoyalr7QBpCkhXZ-Bp0A5ErJXwl_?e=krkpeu

[KNP]

The calculation is correct, your LTC for the top value in the example is 0.4788 not 0.48. (add decimal places to your measure to verify)

If you manually do the calculation with that value, you'll see it is right. 

[KNP]

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]

It is one simple table, no relationships involved

[KNP]

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.

[Anonymous]

OK, I omitted to explain a bit better, but considering the issue (calculation result) that it works for one measure but it gives wrong results for another with same formula.

Still I changed values with min and there is no change in results. I don't know how to add the pbix or data sample to this discussion.

[KNP]

PBIX would need to be shared via OneDrive or similar.

Data you can just copy and paste straight in here.

[katika555]

Why are you using  VALUES function?
just [Tmin (mm)] ??

[Anonymous]

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

[KNP]

Remove the bracket.

[Anonymous]

Still error. It still not my issue here. with values or not, calculation is wrong in DAX! 

[KNP]

That's what I assumed the case may be, so please refer to my first reply. 

Need more detail about your model/relationships to answer this effectively.