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|

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.

  • Are you looking for SUM of Maximum... or total number for each function for each month, quarter, year ? Max of each month or Quarter or Year is always single value ,,, so are you want to get SUM of MAx from each month or Quarter or year or ONLY COUNT? – Rajesh S Sep 12 at 6:39
  • Total for each function for each month (or other metadata values I have in the model) and allow the pivot table to group by year and qtr. For the example I've put in, Function 1 should show as 1.25, Function 2 as 1... and so on, I probably haven't put enough example data in. The measure I've got works for months, but when the pivot table groups by year it isn't showing an expected value. – monkeymagik Sep 12 at 7:55
  • If you are looking for Total for each Function of each Month then use SUMIFS where one criteria is Function & other is Month and you may go for Quarter as well as Yearly also . – Rajesh S Sep 12 at 8:50
  • 1
    Since your sample data is improper so that I'm confused & unable to create solution for the issue!! – Rajesh S Sep 12 at 8:54
  • 1
    Please do not clarify your question in comments; edit your question to make it clearer and more complete.  Specifically, please put desired results into the question. – Scott Sep 13 at 1:37

In power query you can group by function and sum the FTE column to get the total FTE per function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.