topic Re: Unexpected results with DAX ABS function in DAX Commands and Tips

Unexpected results with DAX ABS function

bsmartbkind — Sun, 09 Aug 2020 01:46:01 GMT

Dear Friends.

I need help on some issues that seem very basic but cause me some confusion. I have a column with decimal number type data and with positive and negative values. In order to make some squares with the data source, I have created a calculated column that simply gives me the absolute value of the previously mentioned column. What surprises me is that for some records both columns have different values.

Let's show some measures, formulas and data:

My table is called MA_TRANSACCION

Original Column: Cantidad

Calculated Column: Cantidad_ABS

Cantidad_ABS = ABS(MA_TRANSACCION[Cantidad])

1. Why do these differences occur between the two columns? The only difference I expected was in the sign of the amounts, but not in the amounts.

On the other hand, I have created a measure

Measure 1: Cantidad_ABS_1.

Cantidad_ABS_1 = SUM(MA_TRANSACCION[Cantidad])

With this measure the result is satisfactory. However, the surprise comes when I create a second measure.

Measure 2: Cantidad_ABS_2

Cantidad_ABS_2 = ABS(SUM(MA_TRANSACCION[Cantidad]))

What surprises me in this case is that despite the differences in signs in some registers, the total remains the same for both measures.

2. Why is the total of these two measures equal?

I hope somebody can help me.

Best Regards,

Francisco.

Re: Unexpected results with DAX ABS function

lbendlin — Sun, 09 Aug 2020 00:59:05 GMT

Original Column: Cantidad

Calculated Column: Cantidad_ABS

Cantidad_ABS = ABS(MA_TRANSACCION[Quantity])

That doesn't match. Are you sure you're looking at the right columns?

Your calculated column should be

Cantidad_ABS = ABS(MA_TRANSACCION[Cantidad])

Re: Unexpected results with DAX ABS function

bsmartbkind — Sun, 09 Aug 2020 01:49:58 GMT

Excuse me. I has some problems with the translate. My english is not so good. I already fix that.

Re: Unexpected results with DAX ABS function

mahoneypat — Sun, 09 Aug 2020 13:39:22 GMT

Please try this expression instead for your second measure. Your current one is calculating the same SUM and then taking the absolute value. This one will take the ABS on each row, and then sum the result of all those values.

Cantidad_ABS_2 = SUMX(MA_TRANSACCION, ABS(SUM(MA_TRANSACCION[Cantidad])))

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Unexpected results with DAX ABS function

bsmartbkind — Sun, 09 Aug 2020 18:06:34 GMT

Thank you very much for your answer.

It seems to me that your formula has an error and should be like this:

Cantidad_ABS_2 = SUMX(MA_TRANSACCION, ABS(MA_TRANSACCION[Cantidad]))), it means by taking the SUM out inside the ABS, otherwise the results are incorrect.

However, even so the problem is not solved and we go back to the problem raised in my first question, why does ABS change the value in some records?.

I'll leave you some pictures.

Results with the measure as you suggested it;

Cantidad_ABS_2 = SUMX(MA_TRANSACCION, ABS(SUM(MA_TRANSACCION[Cantidad])))

Result by taking out the SUM inside the ABS;

Cantidad_ABS_2 = SUMX(MA_TRANSACCION, ABS(MA_TRANSACCION[Cantidad]))

Re: Unexpected results with DAX ABS function

mahoneypat — Sun, 09 Aug 2020 19:01:21 GMT

My bad on leaving the SUM inside the SUMX. Silly mistake. The issue you are seeing is weird. Do some C_Numero values have multiple rows? What if you try MIN or MAX to troubleshoot?

Regards,

Pat

Re: Unexpected results with DAX ABS function

Anonymous — Mon, 10 Aug 2020 17:48:49 GMT

It's not possible to say what's going on without access to the data itself.

Re: Unexpected results with DAX ABS function

TalhaAsif — Mon, 19 Dec 2022 05:50:57 GMT

bsmartbkind how you resolved it?