Pivot Table - Error message on summarising the Count field into Sum field

catm

New member
Joined
Apr 28, 2015
Messages
6
Reaction score
0
Points
0
Please can someone help me with this query?
I am trying to create a pivot with a dataset 753 rows and 17 columns.
The problem I am encountering is when I tried to change the “Count” field to a “Sum” for the YTD Mar15 Value.
The below error prompt appears "We cant summarize this field with Sum because it' not a supported calculation for Text data types".
I have tried to change the format of the data to Text, General, Number and Custom but with no success. I even deleted all of the unsupported characters in case these were the problem.
So what is it that I am doing wrong? Can someone help me?
Many thanks,
My-Linh
 
Maybe there are spaces in the numbers, at the end or start. Try doing a Data>Text To Columns on them.
 
Thanks, Bob. This is not the issue. I've checked. There are no spaces in front or behind. I'have also tried to copy the selcted cells to a new workbook altogether but still no success.

Any more ideas as to what could be wrong?

Thank you.

 
Could we see the workbook?
 
Click the Advanced button in the reply, there is a Manager button there.
 
Here is the attached file, Bob.
Please let me know if you can solve this issue.
Many thanks.
 

Attachments

  • Cat mix test.xlsx
    386.3 KB · Views: 246
I notice that you have the data in Power Pivot, was the pivot from this? I notice all the number columns are text, and I couldn't change them. The original data in the spreadsheet is not a table, how did you load it into Power Pivot?
 
Excel 2013 Pro Plus
Got it to work by filling in the 5 no-data rows (blanks) with random numbers.
Also created a Table from the raw data and loaded it into the model. No external source now.
Also used explicit instead of implicit Calculated Fields.
A summary of a summary is usually not a good idea.
http://www.mediafire.com/view/73dyxl3iunzidfb/04_29_15.xlsx
 
Hi Bob,
I have sorted it out now by simply selecting the data again and click on "recommended pivot tables" rather than "pivot table". This seems to have done the job and I could drag all the other fields in. For some reasons the recommended pivot table selection understood what I needed to do with the data and the other selection didn't seem to work because my field headings might be too similar in description.
Thanks again for looking into this issue for me and so prompt in reply.
My-linh
 
How very weird, I have never used recommended pivots before. I wonder how that works?

Would you mind if I submitted this workbook to MS and asked them why?
 
Totally weird. Something as simple as that cost me 2 days of investigative work. Yes, certainly submit to MS, as I am interested in finding out the reasons as to why the option "pivot table" did not work. I am an experienced pivot user so don't usually go to "recommended" option as I want to create my own format and layout as to how I want the table to look. Thanks for your help.

 
Worked for me

Using "recommended pivot tables" worked great. Thank you.

Hi Bob,
I have sorted it out now by simply selecting the data again and click on "recommended pivot tables" rather than "pivot table". This seems to have done the job and I could drag all the other fields in. For some reasons the recommended pivot table selection understood what I needed to do with the data and the other selection didn't seem to work because my field headings might be too similar in description.
Thanks again for looking into this issue for me and so prompt in reply.
My-linh
 
Back
Top