Forum Discussion
Budget vs Commitment vs Actual
Hello dears.
I'm a newbie in Power BI, so I could use a little help here.
I have 3 tables:
1) Budget (with department, value and purchase order columns)
2) Commitment (with value and purchase order columns)
3) Actual (with value and purchase orders columns)
How I should relate those so I can make a "Line and Stacked column" visual like below.
'
Shared Axis: Department
Column series: empty
Column values: Commitment and Actual
Line values: Budget
'
I think I will need to add some dimensions tables, but I'm struggling on it. Would appreciate some help here.
Feel free to suggest anything, even DAX formulas, although I think it will be not necessary.
So each Department has a Budget that includes multiple Purchase Orders, each of different value
A Commitment is a promise of a value for a given Purchase Order
An Actual is an actual value for a given Purchase Order
It looks like the relationships are between the Purchase Order columns... which implies a table of Purchase Orders with a unique entry for each purchase order.
That gives you 3 fact tables: Budget, Commitments, Actuals
with a Purchase Order dimension table that links to all of them with a 1 to many relationship
I'm a personal Power Bi Trainer I learn something every time I answer a question
The Golden Rules for Power BI
- Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
- Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
- Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
- Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result to check on your steps along the way.
Hi Anonymous ,
If your data volume is not very large, I would suggest that you introduce the Commitment value and Actual value into the Budget table so that you can process the data the way you want. Create two calculated columns similar to the following:
Commitment = LOOKUPVALUE(Commitment[Commitment],Commitment[purchase ID],Budget[purchase ID])Actual = LOOKUPVALUE(Actual[Actual],Actual[purchase ID],Budget[purchase ID])Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- kentyler
Solution Sage
So each Department has a Budget that includes multiple Purchase Orders, each of different value
A Commitment is a promise of a value for a given Purchase Order
An Actual is an actual value for a given Purchase Order
It looks like the relationships are between the Purchase Order columns... which implies a table of Purchase Orders with a unique entry for each purchase order.
That gives you 3 fact tables: Budget, Commitments, Actuals
with a Purchase Order dimension table that links to all of them with a 1 to many relationship
I'm a personal Power Bi Trainer I learn something every time I answer a question
The Golden Rules for Power BI
- Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
- Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
- Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
- Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result to check on your steps along the way.
Hello kentyler,
thank you so much for the reply. It worked fine and will mark your answer as a solution.
I don't want to make your workload any heavier, but allow me to ask one more thing.
The "Budget" table have the 'department' column, but the others fact tables don’t, and I have a dDepartment table.
How can I relate those so I can filter/sum the values based on the department for every table?
Thank you in advance.
v-joesh-msftHi Anonymous ,
If your data volume is not very large, I would suggest that you introduce the Commitment value and Actual value into the Budget table so that you can process the data the way you want. Create two calculated columns similar to the following:
Commitment = LOOKUPVALUE(Commitment[Commitment],Commitment[purchase ID],Budget[purchase ID])Actual = LOOKUPVALUE(Actual[Actual],Actual[purchase ID],Budget[purchase ID])Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.