Forum Discussion
Budgets by Department
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
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.
3 Replies
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
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.- 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.
