Pivot Table can't SUM in Data Model

Question

I have an Excel sheet with two tables (that are indeed tables), one with a single-column with employee names, and the other which is an array with the start and end time of each employee and the corresponding worked time that is automatically computed; this second table has various entries for each employee.

Now, I want to create a Pivot Table, included in the Data Model, that sums all the worked hours of each employee. But Excel gives an error when I try to use SUM (or AVERAGE, or any other function that requires numbers): "We can't summarize this field with Sum because it's not a supported calculation for date data types."

But if I create the Pivot Table with the "Add this data to the Data Model" checkbox unchecked, then there's no error. I've checked that the value of the cells in the worked time of each employee is indeed a number, and not a text.

Here's a screenshot of everything:

How can I get the Pivot Table created with the Data Model checked, to SUM the worked hours of each employee?

Answer 1

Delete your pivot table and change your formula to

=([@[End time]]-[@[Start time]])*24

with formatting set to number.

Then that data field should sum with the data model option checked.

Answer 2

Excel 365 with Power Pivot and Power Query.

Discount time clocked-in/out outside working hours.

Discount weekends and holidays if desired.

Discount lunch break.

Aggregate sum (not count) of PQ/PP Durations.

https://www.mediafire.com/file_premium/44dudhq7jd4s6u9/04_30_22.xlsx/file

https://www.mediafire.com/file_premium/92yljafcm9g0pk3/04_30_22.pdf/file

Answer 3

Hi Bernie,

Thanks for your help, but the error still shows. :/

I've uploaded my file to One Drive, if it helps: 4. Sample (2).xlsx

Answer 4

In Excel you are used to being able to calculate everything jumbled together, here it (almost) doesn't matter whether you have a date, a time, a number or in some cases text, the calculation works. Excel is built a bit like "Math for Dummies" / WYSIWYG.

If you use a Data Model this did not work anymore, in here the data type comes into play. Let's take a look:

The data type of your data is a date. It makes no sense to sum up dates, it's like I ask you what is the sum of 14-Feb-2022 and 29-Jun-2024? Hence the error.

In Excel if we sum up times, in fact we do not sum up times. :-) What we sum up is a duration. We format the cell as [h]:mm and we are done. That's what you calculate with the formulas / regular Pivot table.

In Power Pivot (AFAIK) you can not change the data type to Duration, but it is there. How do we get it... later.

We must use a data type that the Pivot table can sum up and that is a decimal number:

That's it. Now we can change the number format of the Pivot field (not the cell format!) to a duration and the PT is ready to go for real life.

Okay, so far so simple, now let us remove the formula from the sheet and let us work with the necessary data only.

A good practice is not to load the data directly into the Data Model, use Power Query to load the data and set the Data type in there. The benefit is that Power Query can do a lot of transformation and has access to many data sources that you can't do/access with Power Pivot.

Data \ Get Data \ From Table

The automatic created Change Type step convert the time into a decimal number and here is the Duration data type. But we do not need that, change the Data type to Time!

Close and load it as connection into the Data Model

Now we can create a PT from the Data Model and we can add a measure to calculate the duration

At the lower left is the format of the measure, but same problem here, no Duration and the PT shows

No problem at all, right-click and choose "Value Field Settings"

In there you can set the number format for a duration as usual and we're done:

Sample file:

https://www.dropbox.com/scl/fi/85v64saoaok0hbdnesqlr/ed8b8a33-c01d-46a3-bd71-7b78d449ad4f.xlsx?rlkey=ii4j11raaygqrxtfbf5tvd0yh&dl=1

Any question?

Andreas.