cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
guilherme_muniz
New Member

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.

2 ACCEPTED SOLUTIONS
kentyler
Solution Sage
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

  1. 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
  2. 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
  3. 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.
  4. 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.

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


View solution in original post

Hi @guilherme_muniz ,

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])

 

21.PNG

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVsRzbV71tVJjhRy58...

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.

 

View solution in original post

4 REPLIES 4
kentyler
Solution Sage
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

  1. 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
  2. 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
  3. 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.
  4. 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.

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


View solution in original post

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.

Hi @guilherme_muniz ,

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])

 

21.PNG

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVsRzbV71tVJjhRy58...

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.

 

View solution in original post

I don't know where your original data is coming from. You will need to find a way there to associate "commitments" and "actuals" with departments. Add the department id to each of those tables and then you will be able to create the relationship you need.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.