Budgets by Department

[Anonymous]

Hi,

I have a table with budgets by department like this:

Budget A	Budget B	Budget C	Budget D	Budget E	Budget F	Total Budget	Total Spent	Total Remaining	Job
100	200	400	300	0	150	1150	850	300	Alpha

And a Table of line items on a job like this:

Amount Spent	Department	Job
50	A	Alpha
250	B	Alpha
350	C	Alpha
0	D	Alpha
0	E	Alpha
100	F	Alpha
100	B	Alpha

How can I model this so that I can build a graph that shows Departments along the X axis with Budget next to Amount spent?

Thanks

[V-lianl-msft]

Hi @Anonymous ,

Just do as @ElenaN  said before, but you need to replace the value ("budget" to blank) that after unpivot other columns.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sturlaws · Accepted solution]

Hi, @Anonymous 

for the most straight forward solution I would rearrange the budget table to this

Department	Job	Budget
A	Alpha	100
B	Alpha	200
C	Alpha	400
D	Alpha	300
E	Alpha	0
F	Alpha	150

And create a Department dimension/table and a Job dimension/table with relationship to both Budget-table and Amount spent-table.

As Total budget is the sum of the department budgets, there is no need to have a separate entry for that in a table. The same goes for total spent and total remaining. You should handle these 3 values as measures. I made a report, based on the data you have provided: .pbix

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[ElenaN]

Hello,

Based on the sample data you provided, you could try to:

- modify the Budget table in PQ, by selecting the last 4 columns and choosing Unpivot other columns.

- Then once you have a row for each budget, split the newly created Budget column so you get a column with the word "Budget" and then another column with the name of your department

- make sure you have a dimension table for both Job and Department

- link the Line items table and the Budget table with Job and Department

- create the Amount Spent measure and the Budget measure

- create the graph as per your needs

Best regards,

ElenaN