topic Budget vs Actual in DAX Commands and Tips

Budget vs Actual

LemonKing — Wed, 27 Aug 2025 08:34:01 GMT

Hi All,

Need some help here as im totally stucked!

I am trying to show the budget vs actual in progress bar using stacked column.

Everything was good but when i have 0 training completed, the stacked column will show 100% achieved and i cant find where is wrong with this.

This is my working:

Training Budget (Total BU) = 824392.95

Training Cost (Total Cost) = 489681.96

Training Grant (Total Grant) = 11174.00

Budget Variance = DIVIDE([Total Cost]-[Total BU]-[Total Grant],[Total BU],"0")

Realised = MIN(1,1+[Budget Variance])

Unrealised = MAX(0,-[Budget Variance])

I have a filter for department so when i filtered the department with 0 training cost, then it will show 100% achieved which is wrong. I have few situation that show wrong 100% achieved:

- when all budget, cost and grant is 0

- when there's budget but cost and grant is 0

Issue 1: It seems the issue occured when my training cost is 0.

Issue 2: Also by right, If i use Budget - Training + Grant then whole thing divide by Budget, i should be getting balance of 42% but my card is showing 39% balance. I have rechecked my filter and all are ok so is there any difference if i use divide vs i use /?

Really appreciate if someone can help me on this..

Re: Budget vs Actual

rohit1991 — Thu, 28 Aug 2025 08:12:00 GMT

Hi LemonKing

I tried your scenario with a sample file and built the progress bar in Power BI. The correct setup is:

1. Achieved % = Actual Capped ÷ Budget

2. Unrealised % = 1 – Achieved %

Where:

1. Actual Used = Cost – Grant (minimum 0, so grant never makes it negative)

2. Actual Capped = MIN(Actual Used, Budget)

3. This logic fixes the issue of showing 100% when Cost = 0. If Budget > 0 and Cost/Grant = 0, Achieved % correctly shows 0%.
4. If Budget = 0 and Cost = 0, the bar goes blank (no false 100%).
5. If overspend happens (Actual > Budget), the value is capped at 100%.

6. In the screenshot below, you can see each department:

IT = 100% (overspend capped)
Finance = 73.3%
Training = 58%
Sales = 16.7%
HR = 0%
Operations = 0%

I also added tooltips with Budget, Cost, Actual Used, and Actual Capped so you can clearly see how the % is calculated. The overall card (53%) shows the weighted total across all departments.

So, using DIVIDE() in the Achieved % measure is the key difference it avoids divide-by-zero problems that caused your wrong 100% earlier.

Re: Budget vs Actual

LemonKing — Wed, 27 Aug 2025 12:46:48 GMT

Hi Rohit1991,

Many thanks for your reply.

I just tried to change the DAX to your way, it works but there's other issues occurs:

1. When i change the department to those with 0 for budget and with training cost, the realised bar is not showing 100%. Is there any way i can show the realised bar to 100% since its exceeded the budget too (although the budget is 0).

2. I tried to do a budget variance then Target Status to show the status for different status, the Target status is showing exceeded budget when all my budget, training cost, grant is 0. I read online, there's this >>= operator but when i use it, then it show error.

1Budget Variance = DIVIDE([1Actual Capped]-[Total BU],[Total BU],"0")

Target Status =

IF(

[1Budget Variance]>=0,

"Exceeded Budget",

"Within Budget"

)

Do you have any idea how to make this work?

Re: Budget vs Actual

rohit1991 — Wed, 27 Aug 2025 12:53:35 GMT

Hi LemonKing

1. For the % Achieved not showing 100% when the actual is higher than budget >> you can wrap the measure with a MIN(1, …) logic so it never exceeds 100%. Example:

Achieved % = VAR Ach = [Actual Capped] VAR Bud = [Budget] RETURN IF ( Bud = 0, BLANK(), MIN ( 1, DIVIDE ( Ach, Bud ) ) )

2. For your Budget Variance logic, try restructuring it with SWITCH(TRUE()) so it handles each case clearly:

Budget Variance = VAR Actual = [Actual Capped] VAR Budget = [Budget] RETURN SWITCH ( TRUE(), ISBLANK ( Budget ), BLANK(), Actual > Budget, "Exceeded Budget", Actual = Budget, "Within Budget", Actual < Budget, "Under Budget" )

This should solve both the 100% cap issue and the condition check.

Re: Budget vs Actual

LemonKing — Thu, 28 Aug 2025 09:08:18 GMT

Hi Rohit1991,

I have tried the formula that you shared but its not working for both.

1. The achieved% is still showing 0 for situation where i got training cost but without budget. It should be the other way round.

1Achieve% =

VAR Achieve=[1Actual Capped]

VAR Budget=[Total BU]

RETURN

IF(

Budget=0,

BLANK(),

MIN(1,DIVIDE(Achieve,Budget))

)+0

2. According to your formula, its good when all my budget, cost and grant is 0, it return Within Budget. But when there is a cost with no budget, it show within budget too which is wrong.

I have tried to change it to Actual>=Budget, "Exceeded Budget", it works for those with cost and no budget as its showing Exceeded Budget but when its all 0 in budget, cost and grant then it show Exceeded Budget too which is wrong and i think its back to the same issue i have in the first time.

1Budget Variance =

VAR Actual = [1Actual Capped]

VAR Budget = [Total BU]

RETURN

SWITCH(

TRUE(),

ISBLANK(Budget), BLANK(),

Actual>=Budget, "Exceeded Budget",

Actual=Budget, "Within Budget",

Actual<Budget, "Under Budget"

)

Is there other way i can do this?

Re: Budget vs Actual

LemonKing — Mon, 01 Sep 2025 02:14:43 GMT

I found the issue for this as Actual Capped is the one causing all these issue.

After i replaced Actual Capped with Actual Used then it solved all the issues for me.

Solution for No 1:

Realised =

IF(

[Total BU]=0,

BLANK(),

MIN(1,DIVIDE([Training Actual Used],[Total BU])

)+0)

Solution for No 2: Replaced Actual Capped with Actual Used and changed the order of the status that i needed since SWITCH order will be affected depending on where i placed the status. Eg if Actual=0, "No Training" is placed at the last row, then it wont work anymore.

Target Status =

VAR Actual = [Training Actual Used]

VAR Budget = [Total BU]

RETURN

SWITCH(

TRUE(),

ISBLANK(Budget), BLANK(),

Actual=0, "No Training",

Budget=0 && Actual>Budget, "Exceeded Without Budget",

Budget=0, "No Budget Allocated",

Actual>Budget, "Exceeded Budget",

Actual<Budget, "Within Budget"

)

Thanks rohit1991 for your time too. Without your help to provide the correct DAX, i wont be able to solve this.