Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
bsmartbkind
Advocate I
Advocate I

Unexpected 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])

 

Anotación 2020-08-08  Valor Absoluto en Columna Calculada 01.png

 

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.

 

Anotación 2020-08-08  Valor Absoluto en Columna Calculada 02.png

 

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

 

I hope somebody can help me.

 

Best Regards,

 

Francisco.

 

Anotación 2020-08-08  Valor Absoluto en Columna Calculada 03.png

7 REPLIES 7
Anonymous
Not applicable

It's not possible to say what's going on without access to the data itself.
mahoneypat
Microsoft Employee
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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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])))

 

Anotación 2020-08-09 Resp2 01.png

 

Result by taking out the SUM inside the ABS;

 

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

 

Anotación 2020-08-09 Resp2 02.png

 

 

 

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


lbendlin
Super User
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])

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

@bsmartbkind how you resolved it?

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.