Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm looking to create a static annual budget and use it for comparison purposes.
So my data is committment accounting and therefore is always a full year forecast split across multiple teams and months/weeks. I have an annual budget split by team, which does not change throughout the year.
What i'd like to do is have that static budget column each time when analysing the team data regardless of the month and then be able to do one minus the other.. a bit like the below:
Team | Forecast Cost | Budget | Variance |
1 | 400 | 300 | (100) |
2 | 450 | 400 | 50 |
3 | 500 | 300 | (200) |
4 | 300 | 400 | 100 |
I've no doubt this is possible but anyones help woule be much apprecaited.
Thanks
Colin
Hi @colin_dickson ,
You can create a separate table to act as a static budget
Here are the steps you can follow:
1. Enter data.
2. Create calculated column.
Variance =
ABS(
[Forecast Cost]
-
SUMX(
FILTER(ALL(Table2),
'Table'[Team] in SELECTCOLUMNS('Table2',"1",'Table2'[Team])&&
'Table2'[Team]=EARLIER('Table'[Team])),
'Table2'[Budget])
)
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@colin_dickson , For static budget you can create a measure like
Budget = 1000
of use numeric parameter https://docs.microsoft.com/en-us/power-bi/desktop-what-if
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |