Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi, review the Attached PBIX Page 4. Is very close of the expected result.
Regards
Victor
Hi, try with this measure:
M-ContractHours.Assigned Hours = IF ( HASONEVALUE ( TimeCardContractHoursMerged[Date] ), BLANK (), AVERAGE ( TimeCardContractHoursMerged[ContractHours.Assigned Hours] ) )
Regards
Victor
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:
Employee | Hours Worked | Assigned Hours | |
Jennifer Smith | 115 | 85 | |
Alpha | 18 | 25 | |
Friday, May 4 | 10 | ||
Saturday, May 5 | 8 | ||
Beta | 32 | 20 | |
Monday, May 7 | 7 | ||
Monday, May 8 | 6 | ||
Monday, May 9 | 11 | ||
Delta | 33 | 40 |
Hi, review the Attached PBIX Page 4. Is very close of the expected result.
Regards
Victor
Thanks, Vvelarde! The TREATAS function was what I needed.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |