Power Pivot Principles: The A to Z of DAX Functions – ABS
15 June 2021
In our long-established Power Pivot Principles articles, we are starting a new series on the A to Z of Data Analysis eXpression (DAX) functions. So how about take a brake and read about ABS…
The ABS Function
When the ABS function is applied it returns the absolute value of a number, generated by a formula, measure or parameter. The absolute value is the numerical value excluding sign (i.e. the notation that determines whether it is positive or negative). You might think of it as a useful way to determine the distance between two points, ignoring direction.
The syntax is very simple:
=ABS(number)
To illustrate the concept:
For those who may get an error back from the ABS function, it is possible that that the number you used was actually a text value: ensure that the number is numeric.
Example
Imagine you are an analyst that wishes to assess how accurate forecast sales were against the revenue actually achieved. You might record a list as follows:
Your task is to identify the overall discrepancy between forecast and actual sales, regardless of sign. This is not to be performed on a record by record basis, but in aggregation. Therefore, you would first import the data (let’s imagine the table is called Sales) into the Data Model and create two measures to represent both financial fields:
Actual Revenue := SUM(Sales[Actual Sales])
and
Forecast Revenue := SUM(Sales[Forecast Sales])
The absolute discrepancy could then simply be measured using,
Absolute Forecast Error := ABS([Actual Revenue] – [Forecast Revenue])
This would generate a non-negative number, regardless of which measure was the larger (if either).
Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.
