Cannot get desired Matrix visualization subtotals and totals

[AdamOrmond]

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.

[Vvelarde]

@AdamOrmond

Hi, try with this measure:

M-ContractHours.Assigned Hours =
IF (
    HASONEVALUE ( TimeCardContractHoursMerged[Date] ),
    BLANK (),
    AVERAGE ( TimeCardContractHoursMerged[ContractHours.Assigned Hours] )
)

Regards

Victor

[AdamOrmond]

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

[Vvelarde · Accepted solution]

@AdamOrmond

Hi, review the Attached PBIX Page 4. Is very close of the expected result.

Regards

Victor

[AdamOrmond]

Thanks, Vvelarde! The TREATAS function was what I needed.