Budget vs Commitment vs Actual

[Anonymous]

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.

[kentyler · Accepted solution]

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.

[kentyler · Accepted solution]

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.

[Anonymous]

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-msft · Accepted solution]

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.

[kentyler]

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.