cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
spmvoss
Regular Visitor

Compare logged hours to planned hours

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 IDMain ProjectWork PackageDateEmployerHours
1Project 1WP 101-01-2020emp12
2Project 1WP 201-01-2020emp14
2Project 1WP 201-01-2020emp25
1Project 1WP 102-01-2020emp14
3Project 2WP 102-01-2020emp23

 

and another table that functions as the projects database

Project IDMain ProjectWork PackagePlanned HoursDuration (weeks)
1Project 1WP 12512
2Project 1WP 23012
3Project 2WP 1409
4Project 2WP 2309

 

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?

1 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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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])))
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors