Budget vs Actual

[LemonKing]

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.. 

[LemonKing · Accepted solution]

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. 

[rohit1991]

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.

[LemonKing]

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?

[rohit1991]

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.

[LemonKing]

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?