Compare logged hours to planned hours

[spmvoss]

I am trying to show a visual that shows actual logged hours per project vs the planned hours. I have two tables, one is an export from the time logging software.

Project ID	Main Project	Work Package	Date	Employer	Hours
1	Project 1	WP 1	01-01-2020	emp1	2
2	Project 1	WP 2	01-01-2020	emp1	4
2	Project 1	WP 2	01-01-2020	emp2	5
1	Project 1	WP 1	02-01-2020	emp1	4
3	Project 2	WP 1	02-01-2020	emp2	3

and another table that functions as the projects database

Project ID	Main Project	Work Package	Planned Hours	Duration (weeks)
1	Project 1	WP 1	25	12
2	Project 1	WP 2	30	12
3	Project 2	WP 1	40	9
4	Project 2	WP 2	30	9

Now for the visual I have selected the Hours and Work Package column from the first table. The user can select the main project from a selector. This works as expected and shows a bar graph with the hours per Work Package.

If I select the Planned Hours and Work Package from the other table it also does what expected, it shows a bar graph with the expected values from the project database table.

However, when I try to add the Planned Hours to the first visual, it just sums up all the hours by Main Project, instead of also grouping it by Work Package. I cannot figure out how to make this work. Does anyone have any recommendations?

[amitchandak]

@spmvoss 

If the second table is the master table

Then in both table create a new column

Key = [Main Project] & "-"& [Work Package]

And join them.

Else create a new dimension table  with same key and join them

Dimension table

distinct(
union(	
selectcolumn(Table1,"Project ID",Table1[Project ID],"Main Project",Table1[Main Project],"Work Package",Table1[Work Package],"key",Table1[Main Project] & "-"& Table1[Work Package]),

selectcolumn(Table2,"Project ID",Table2[Project ID],"Main Project",Table2[Main Project],"Work Package",Table2[Work Package],"key",Table2[Main Project] & "-"& Table2[Work Package])))

[spmvoss · Accepted solution]

Thank you for your reply!

I realised I actually already had a key column like that, the project ID, since they were unique. So I wasn't sure what you meant by joining them first but then decided to check the model view and noticed there was no relationship between the two tables.

Simply dragging between the project ID labels between the two tabels and setting the direction to both solved the problem and functionality is exactly as expected now!

Thanks so much for your input