Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Percentage Subtotals on Matrix Incorrect

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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.