Budget vs Commitment vs Actual

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.

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:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVsRzbV71tVJjhRy5852_qEBqv9WwwzR03ED1ELTOP6QtQ?e=Ny0eVq

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.