Error: 'We can't summarize this filed with Sum because it's not a supported calculation for Text data types.'

Mikes_KM

New Member
Joined
Jun 16, 2019
Messages
17
Hey Everyone,

I am trying to attach a slicer to 2 Pivot Tables based on different data sources. I am following the method as shown in this Youtube video https://youtu.be/p9VD0rsC_bk . I am using it as a guide for my own excel spreadsheet.

For my own spreadsheet, I have ticked the box where it says "add this data to the data model" as shown in the Youtube video at 2:13

Under the 'Pivot Analysis Tab', I have put 'Week #' into Rows and also put Account M/M into Values. However, it comes up as a Count of Account M/M into Values. Within value field settings it wont allow me to change Sum or Average.

When I try to change it to sum or average, I get the following error message:
We can't summarize this filed with Sum because it's not a supported calculation for Text data types.


How do I solve this issue?
It should ideally produce columns identical to the pivot table 'Equity_Pivot' as shown in columns A and B. I have highlighted above both pivot tables in yellow to show you what it should look like. Note: [Just as a reminder, the end goal is to connect a slicer to 2 pivot tables from different data sources. That is why I am recreating this pivot table.]

Spreadsheet link - https://drive.google.com/file/d/1X4AiNBWku7Z-lyKCuiXPA6izgDKeKEmX/view?usp=sharing
The file is quite large.

I hope this is clear, thanks in advance for your help ! :)
 
Last edited:
Remove any extra rows to your current data Range.
Make sure your data range has no Blank Columns or Blank Rows elsewhere in the data Range.
Make Sure there is a unique Header Name for Each Column and that each header is in the same Single-Row.

Select one cell in the data Range
Hit CTRL + *
This will select the contiguous Range. make sure the range selected is your current data.
Hit CTRL + T
This will convert the Range to a Table (I use Table for a Table object vs table being just a range.)
You can rename the Table.

For your Pivot Table, change the the data source selecting all the cells of the Table. You should see the Table name referenced instead of a Range Reference. So instead of seeing something like $A$1:$F$390 you should see a reference of Table1

Adding data to the Table, next row, will be added to the Table. The Pivot Table will need the Refresh action to update the source, but you will not need to redefine the area from where the data is sourced.
Formulas in a Table will automatically fill down
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top