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

[Mikes_KM]

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 !

 

[SpillerBD]

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