Summarizing from 2 different tables

[Anonymous]

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.

[FrankAT]

Hi @Anonymous 

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

The rest i swith DAX:

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)

[Anonymous]

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.

[PaulDBrown]

@Anonymous 

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)

[vanessafvg]

not really sure what you looking for but see attached.

 i joined on civil structure, see attached.

[vanessafvg]

can you provide the data in text format?

[Anonymous · Accepted solution]

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

[vanessafvg]

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?