Percentage Subtotals on Matrix Incorrect

[Anonymous]

I'm building a capacity planner and I cannot for the life of me get the subtotal percentages to work.  In the matrix below, the main values are correct, but the subtotals for rows and columns and the grand total are not.  There are 2 tables, the first contains a single row for each employee for each month per project and role.  The table below has the hours summed by employee and month.  The percentages shows how much capacity is used.  PTO hours are removed and then the rest of the hours are divded by the total hours available for the month minus the PTO hours.  The total hours available are calculated from a date dimension by multiplying working days (weekdays minus holidays) times 8.

Calculations:

TotalWorkingHours = SUMX(FILTER('Capacity Planner','Capacity Planner'[Role]<>"Hours NOT Available"),'Capacity Planner'[Hours])

HoursAvailable = MAX(DateTable[WorkingHoursAvailable]) 
     - SUMX(FILTER('Capacity Planner','Capacity Planner'[Role]="Hours NOT Available"),'Capacity Planner'[Hours])

WorkingHours%Used = DIVIDE([TotalWorkingHours],[HoursAvailable])

I tried various ways to build a seperate measure for the subtotals using all or allexcept and found no luck.  Any help is appreciated.

[Greg_Deckler · Accepted solution]

@Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous]

Thanks! That pointed me in the right direction.  I needed 2 HASONEFILTER functions to get both the columns and row subtotals correct.  

HoursAvailable = IF(HASONEFILTER('Capacity Planner'[ResourceName]),IF(HASONEFILTER('Capacity Planner'[EndOfMonth]),
                    MAX(DateTable[WorkingHoursAvailable]) - [HoursFiltered],
                    DISTINCTCOUNT('Capacity Planner'[EndOfMonth]) * MAX(DateTable[WorkingHoursAvailable]) - [HoursFiltered]),
                    DISTINCTCOUNT('Capacity Planner'[ResourceName]) * MAX(DateTable[WorkingHoursAvailable]) - [HoursFiltered])

Hoursfiltered is a new measure that contains the sumx from above to help with readability.  Any idea how I would get the grand total corect?   I probably just need to reorder the ifs, but my logic brain isn't getting it at the moment:

[Anonymous]

I needed to multiply the two distinct count columns together on both hasonefilters to get the correct values.  It took way too long to get to that logic in my brain, but it makes sense because when there's subtotals, it's multiplying by 1 and for the grand total, it's the total number of cells in the matrix.