cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bw70316 Member
Member

Absolute Value Except when % exceeds 100%

I have a budgets and actuals table with revenue items that are coming as negative. I put an Absolute value formula on these. However, I am trying to filter through the Revenues Items where Incoming Revenue has exceeded the budget and have those values appear as negative. So basically I want a DAX equation that Filters the outputs the Rev Remaining as negative When Abs Spent > Abs Rev Budget. That way I can highlight that the revenues have exceeded the budgeted amount without having to highlight the % SpentRevenue Abolute Value.png 

 

Abs Rev Budget = ABS([Total Budget])
Remaining = SUM('Budgeted Items'[_ActualBudgetAmt]) - SUM('Actual Items'[Spent])
Abs Rev Remaining = ABS([Remaining])
Total Spent = SUM('Actual Items'[Spent])
Abs Spent = ABS('_Measures'[Total Spent])
Total Budget = SUM('Budgeted Items'[_ActualBudgetAmt])

 

1 REPLY 1
Community Support Team
Community Support Team

Re: Absolute Value Except when % exceeds 100%

Hi @bw70316

 

You may create a measure with IF condition.For example:

Measure =
IF (
    [Abs Spent] > [Abs Rev Budget]
        && [Remaining] < 0,
    [Remaining],
    [Abs Rev Remaining]
)

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.