I have a dataset in Excel that I need to summarize. The source table is in a format like below:
|Role title|Function |Manager |Month 1|Month 2|Month 3| |Role 1 |Function 1|Manager Name|0 |1 |1 | |Role 2 |Function 1|Manager Name|0.25 |0.25 |0.25 | |Role 3 |Function 2|Manager Name|0 |0 |0 | |Role 3 |Function 2|Manager Name|1 |1 |1 | |Role 4 |Function 3|Manager Name|0 |0 |0 |
The value in each month is a value from 0 to 1 whether a staff member is fulltime or part time. I've used PowerQuery to unpivot and format the data so I can use it in a pivot table from the Excel Data Model
|Role title|Function |Manager |Month |FTE | |Role 1 |Function 1|Manager Name|Month 1|0 | |Role 1 |Function 1|Manager Name|Month 2|1 | |Role 1 |Function 1|Manager Name|Month 3|1 | |Role 2 |Function 1|Manager Name|Month 1|0.25| |Role 2 |Function 1|Manager Name|Month 2|0.25| |Role 2 |Function 1|Manager Name|Month 3|0.25| etc...
What I would like to summarize is the total number for each function for each month, quarter, year, etc.
I've created a measure in PowerPivot to calculate the sum of the maximum values for each which is:
SUMX(VALUES[Role Title]), CALCULATE(MAX[FTE])))
The measure works ok for the months and quarters in the pivot table, but the total for each year isn't showing the correct amount. I think I need to wrap an IF statement around the measure, but not sure what the alternate equation should be.