cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SKH19 Member
Member

Budgets by Department

Hi,

I have a table with budgets by department like this:

Budget ABudget BBudget CBudget DBudget EBudget FTotal BudgetTotal SpentTotal RemainingJob 
10020040030001501150850300Alpha

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

Amount SpentDepartmentJob
50AAlpha
250BAlpha
350CAlpha
0DAlpha
0EAlpha
100FAlpha
100BAlpha

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
Super User III

Re: Budgets by Department

Hi, @SKH19 

 

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

DepartmentJobBudget
AAlpha100

B

Alpha200
CAlpha400
DAlpha300
EAlpha0
FAlpha150

 

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.

 

View solution in original post

3 REPLIES 3
ElenaN Member
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
Super User III

Re: Budgets by Department

Hi, @SKH19 

 

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

DepartmentJobBudget
AAlpha100

B

Alpha200
CAlpha400
DAlpha300
EAlpha0
FAlpha150

 

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.

 

View solution in original post

Microsoft V-lianl-msft
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.
test_0128.PNG
 
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors