Wrong Totals in a conditional measure

[Anonymous]

I have a conditional measure that is showing the wrong subtotals and totals in a matrix visualization.

The measure works like this:

If a worker is a contractor then I have to add all the hours they logged in Schedule. If the worker is not a contractor then I have to take the Defined Month Hours from the Calendar table. See example formula below:

Month Hours =IF(MAX(WorkerData[Type])="Non Contractor", SUM('Calendar'[Defined Month Hours]),CALCULATE(SUM(Schedule[HOURS]))

If I check each worker everything works correctly, but when checking the totals or subtotals, only Non Contractors are being added.

See example:

Boss	Worker	Month Hours
Boss Subtotal		100
	Worker 1 (Non contractor)	50
	Worker 2 (Non contractor)	50
	Worker 3 (Contractor)	5
Total		100

I would appreciate any help with the issue was I have been finding solutions but not for this specific type of scenario.

Thanks.

[Ashish_Mathur]

Hi,

Does this measure work?

Measure = SUMX(VALUES(WorkerData[Type]),[Month hours])

[ppm1 · Accepted solution]

Please see this video for how to solve this. You likely need to use something like this (replacing T1 with your actual table name.

NewMeasure = SUMX(Summarize(T1, T1[Boss], T1[Worker]), [YourMeasure])

(2) Power BI - Tales from the front #01 - Getting the Right Total - YouTube

Pat

[Anonymous]

I am afraid that the boss and the worker are at different tables.