When I work with DAX and Power Query, I find that some of the most useful functions are also the simplest ones, and ABS is a good example of that. It doesn't do anything complicated: it takes a number and returns its absolute value, meaning the number stripped of its sign. A negative value becomes positive, and a positive value stays unchanged.
The syntax is straightforward: ABS(<number>)
The argument can be a literal number, a column reference, or the result of another expression. DAX evaluates whatever you pass in and returns the magnitude of that value.
Absolute Variance = ABS([Actual] - [Budget])
At first glance, this might seem too basic to matter much in a data model, but it turns out to be one of the more practical tools available once you're working with any dataset that mixes positive and negative values which is more common than it might seem. Financial transactions, variance reports, inventory adjustments, and returns data all tend to include both debits and credits, gains and losses, additions and subtractions. In many of these cases, what you actually want to measure is the size of the change, not its direction.
Common Use Cases
Variance analysis. When comparing actuals to budget, or forecasts to results, the direction of the variance (over or under) is often less important than its magnitude when you're trying to rank or flag the largest discrepancies. ABS lets you sort or filter by size without the sign getting in the way.
Threshold and tolerance checks. Instead of writing two separate conditions to catch values that are "too high" or "too low," ABS lets you express both in a single comparison.
Out of Tolerance = IF(ABS([Actual] - [Target]) > 100, "Flag", "OK")
Normalizing inconsistent data. Some source systems record certain transactions as negative and others as positive depending on how they were entered. ABS can standardize these for reporting purposes, though it's worth investigating the root cause rather than treating it as a permanent fix.
Distance-based calculations. Any time you need to measure how far a value sits from a reference point, such as an average or midpoint, in either direction, ABS is the natural function to use.
An Important Caveat
ABS removes information. Once a value has been passed through it, you can no longer tell from that number alone whether the original was positive or negative. This matters because a variance of $500 could mean a department overspent or underspent, and those are very different situations even though the absolute values are the same.
A pattern I rely on is to keep the signed value as the source of truth, and create a second measure purely for sorting, ranking, or visual thresholds.
Variance = [Actual] - [Budget]
Abs Variance (sorting) = ABS([Variance])
Variance Direction = IF([Variance] >= 0, "Over", "Under")This way, the report can sort by magnitude while still communicating direction separately, whether through a label, an icon, or color formatting.
Join the conversation! Your thoughts help the community grow.