Forum Discussion
Budget vs Actual
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:
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]RETURNSWITCH(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.
5 Replies
rohit1991Super User
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.
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.
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]RETURNSWITCH(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.