Forum Discussion
Advocate IUnexpected results with DAX ABS function
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.
7 Replies
- lbendlin
Super User
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])
bsmartbkindExcuse me. I has some problems with the translate. My english is not so good. I already fix that.
bsmartbkind how you resolved it?
- mahoneypat
Microsoft Employee
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
- bsmartbkind
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]))
- mahoneypat
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
