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

Summarizing from 2 different tables

Hi There, 

 

I want to make a visualization from some data.  I want to set of the actual + forecasted (man)hours versus the available budget.

Both the actual manhours & forecasted manhours are in seperate tables.  I want to make the summary per civil structure (i work in construction).  So actually I think I have to make a new table (or column) wich summarizes the total hours (both actual + forecasted) in a this new table (or colum).   I can do this very easily in excel but I don't seem to get the solution in Power BI.  

Probably very easy but I am new to powerBI so still learning every day.   For the sake of the solution I have included a simplified version of the 2 tables in which I have the data.   

 

Thanks for any suggestions.

 

Tables for problem.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi

 

These tables are really a simplified version of the real data set.  

But hopefuly the replies can give me a start on solving my problem.

Can't seem to add an attachment to the reply? 

 

Table : Manhours
Civil_Structure Week Actual_Manhours
1 8 20
2 8 20
3 8 20
1 9 20
2 9 20
3 9 20

Table : Forecast
Civil_Structure Week Forecasted Manhours
1 10 80
2 10 40
3 10 80
1 11 60
2 11 40
3 11 30

 

 

View solution in original post

7 REPLIES 7
Highlighted
Super User I
Super User I

can you provide the data in text format?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

Hi

 

These tables are really a simplified version of the real data set.  

But hopefuly the replies can give me a start on solving my problem.

Can't seem to add an attachment to the reply? 

 

Table : Manhours
Civil_Structure Week Actual_Manhours
1 8 20
2 8 20
3 8 20
1 9 20
2 9 20
3 9 20

Table : Forecast
Civil_Structure Week Forecasted Manhours
1 10 80
2 10 40
3 10 80
1 11 60
2 11 40
3 11 30

 

 

View solution in original post

Highlighted
Super User II
Super User II

Hi @bartdesmedt 

first I build a new table with unique civil structure values.

The rest i swith DAX:

 

24-09-_2020_20-08-47.png

Sum of Actual Manhours = SUM(Manhours[Actual Manhours])

Sum of Forcasted Manhours = SUM(Forecast[Forecasted Manhours])

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Highlighted

ok the issue i am seeing here is how do you join this data?

 

you have different forecast weeks to manhour weeks, did you mean to do that?

 

otherwise its fairly easy to merge these tables based on their civil structure, but i assume you would want it in the same week?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

ok. And in which table do you then make the sum of both sums?

I want to have the sum of actual hours + sum of forecasted hours as 1 number per civil structure.

Highlighted

not really sure what you looking for but see attached.

 

 i joined on civil structure, see attached.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

@bartdesmedt 

The simplest solution is to follow @FrankAT  suggestion of creating a dimension table with unique values for the "civil Structure" field and join this diemnsion with each of your tables in a one-to-many relationship joining the respective "civil structure" fields. 
the create your final  table visual using the "civil structure" field from the dimension table and the SUM measures as suggested. 
(BTW, you should also create a "week" table as a dimension to use in your final visual if you need comparisons by week)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors