Pivot Tables

Copper Contributor

I have a table named Hours with three columns; Cost Code, Description, Allowance.

I have another table named Tracked Hours on a different sheet that has four columns; Employee (drop down using data validation), Cost Code (list from the Hours table), Hours and Date.


I am using these tables to keep track of our employees hours against our budgeted hours for a project.


I created a pivot table to summarize the tracked hours that shows how many hours each employee has used from each cost code. I would like to add the allowance column from the Hours table and show a total hours remaining column in my pivot table but I cannot figure out how to incorporate it in my pivot table. 


I would like my pivot table to show a row for each cost code, with rows for each employee and how many hours they've used from the total allowance. Then I would like a total row or column that shows how many hours are left from my allowance. I am open to suggestions or a better way of presenting this data.


Please advise.


I have attached a sample spreadsheet of what I have done.





4 Replies

@rdwmatt First you need to create a relationship between the two tables in the Data Model. Then you also need a few DAX measures that let you summarize the hours by Employee and calculate the overall Allowance and Allowance Remaining.


I've done that in the attached file.

Can you expand on your answer so I know how exactly this works and how to do it myself in the future. A step by step guide would be extremely helpful.

Thank you.

@rdwmatt I'm sorry, but a step-by-step guide explaining how it all works goes beyond the scope of my involvement in this user forum. I don't have time for that.

But you could invest some time yourself learning basic DAX. Learn what measures are and then use on-line resources to read about the functions used in my examples. Though, be prepared that DAX is far from intuitive or user friendly. At least, that's my personal opinion. It takes a considerable effort to master it. An I am still a long way away from that point.

Thank you, I appreciate the help. I will look into DAX and study up.