Summarise data is greyed out in pivot table

[ewilson378]

Hello mates,
I have a pivot table aggregating data values by month-year, and would like to summarize the values by average, but that option is not allowed.
Perhaps the main culprit is that the data in the column originally is text format (text+number - P18001001 for example). I changed the data type in source file and refreshed the Pivot Table, but still not able to summarize because it's still being read as text. I even created a measure in Pivot Table fields, CountA(column I'm trying to summarize), set the format to number, used the measure in pivot table, it displays the same data, but still not able to summarize the data.
Any suggestions/ideas?

[kersplash]

Can you attach a small sample of your data and pivot table showing the issue?

Go Advanced -> Manage Attachments -> Upload

[ewilson378]

Hi kersplash,
thank you for your reply. I've tried to shrink the file, even compressed it to 1.3mb, but it's still exceeds the file size limit to attach (1mb). any suggestions? can I send to your email directly?

[kersplash]

Remove most of the data, leaving enough to show the problem. Refresh the pivot table.

[ewilson378]

I eliminated most of the data, left only a few rows, deleted the pivot chart and only left the refreshed pivot table and still over 1mb, compressed.

[kersplash]

You must have other data you don't know about.

Copy the small dataset to a brand new workbook and create the pivot table.

[ewilson378]

I checked for hidden files, nothing. I followed your suggestion and amazing! That worked, beautiful. Thanks. I still would like to find out why the difference in file size.
Anyway, so the option to Summarize is available now, but I'm getting an error type in the cell.
I want to see the average enrollments within the time frame (number of days), but I guess the function doesn't know that?
Now how do I attach files I've uploaded?

[ewilson378]

PS - I see it attached

[kersplash]

OK. Now maybe add a slightly larger sample and also show (mock-up) what you expect to see as your results for that sample.

ie. What are you trying to average?

[ewilson378]

Hi,
I'm trying to average the count of visits(Participants ID column) per month based on the amount of days of activity in the month, not days in the month, in the Pivot Table.
In the pivot table, if I expand any of the sections (Collection Sites)and I highlight the Count of Participants column, I get number of days of activity, the count of participants per day, and the average in the bottom status bar section. How can I get that calculation without having to do this manually?

[stasinek]

Try to group dates for months

[ewilson378]

Hi Stasinek,
thank you for the reply, but how does that help me?
If I group by month, I won't be able to see how many days of activity, which will make showing data as average not work, and that's my current problem.

[kersplash]

This may be what you need;

https://www.excelcampus.com/pivot-ta...l-calculation/

[stasinek]

Of course, a friend kersplash is right.

[ewilson378]

Yes, thank you, both!!!