cancel
Showing results for
Did you mean:
Highlighted
Member

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III

## Re: Budgets by Department

Hi, @SKH19

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 3
Member

## Re: Budgets by Department

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

Super User III

## Re: Budgets by Department

Hi, @SKH19

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.

Microsoft

## Re: Budgets by Department

Hi @SKH19 ,

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.

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors