I'm working on building a matrix visualization showing employee time expenditures against assigned contract hours. For instance, Jennifer Smith has been assigned hours across three contracts. Each day she spends some number of hours working on each contract. I'd like to see running totals for the number of hours spent and the number of hours remaining (assigned - spent).
To implement this visualization, I have a table containing timecard data where each row shows the number of hours spent that day against a specific contract. In addition, I have a table containing assigned hours for each employee and each contract. I have tried merging these tables together, giving me a single table with a row containing hours spent and hours assigned for each employee and day, but the result is not as desired.
For instance, if we look at Jennifer Smith, we see the below. She’s not assigned 50 hours for the Alpha contract, though. She’s assigned 25, but when the two tables are merged the assigned hours appears in every row. This occurs for each contract: Beta she is assigned 20, not 80. Delta she is assigned 40, not 160.
I have uploaded an Power BI page with sample data here: Power BI Sample. Page 3 contains my most recent attempt at producing the desired output.
Unfortunately that does not produce the desired output. It does show the average value for the day, 25 for Jennifer/Alpha, but the total values for the employee and across all employees do not add up. This is the desired result looks something like this for Jennifer Smith: