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
16
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:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
It must be reading the numbers as text, have you tried a simple is text or is number against the column to see what the values actually are?
 

Mikes_KM

New Member
Joined
Jun 16, 2019
Messages
16
@Dryver14 Thanks for a prompt response.
I went to the data source (Table 2) under the money management tab. I ran an =Isnumber against the cells under Account M/M in column I.
They all said TRUE, so it seems that they should all be numbers.

However, the formula is =IF([@[Closed Pips]]="","",Starting_Account_Balance+[@[Profit/Loss M/M]])
Am I getting the error due to the blank data "" under column 210?

I tried deleting the blank data but it messes up the whole sheet.
 
Last edited:

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
I'm not sure that it is a good idea to have the count column to the left can you insert a column between the count and the rest of the table then work from there?, not sure why this would affect numbers appearing as text though
 

Mikes_KM

New Member
Joined
Jun 16, 2019
Messages
16

ADVERTISEMENT

I'm not sure that it is a good idea to have the count column to the left can you insert a column between the count and the rest of the table then work from there?, not sure why this would affect numbers appearing as text though

Sorry, I am a little confused by what you mean/are suggesting here.

If you are suggesting putting the Account M/M into the left (Rows) section of a pivot table. Then I have just tried that but it is not displaying the data correctly. I want to produce an equity curve which tracks the Equity (Account balance) as I trade week by week.
 
Last edited:

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
No I'm just suggesting that the table has a gap all the way around the edges so it reads properly.
 

Mikes_KM

New Member
Joined
Jun 16, 2019
Messages
16

ADVERTISEMENT

No I'm just suggesting that the table has a gap all the way around the edges so it reads properly.

You just gave me an idea which has worked. THANKS!
When I create the pivot table, instead of using the whole of table 2 as my 'Table/Range' I selected all of the data up to the last row filled with data. Meaning I did not include the blank cells below Row 211.

This has worked now. However, it leads to another question lol. Will my pivot table be dynamic? Lets say I add another column of data ... 212, 213, 214 etc. Will the pivot table adjust automatically?
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
If a PivotTable is based on a Table-Object, then yes.
You can also build a PivotTable from a Get and Transform Query (ie PowerQuery) and build a Pivot Table from a DataModel. These are all dynamic when the Refresh occurs. However, understanding how the and when the refresh is done manually or automatically is important.
 

Mikes_KM

New Member
Joined
Jun 16, 2019
Messages
16
If a PivotTable is based on a Table-Object, then yes.
You can also build a PivotTable from a Get and Transform Query (ie PowerQuery) and build a Pivot Table from a DataModel. These are all dynamic when the Refresh occurs. However, understanding how the and when the refresh is done manually or automatically is important.


Forgive me, I am not the best with excel jargon. I want to remove all of the rows below the last filled out row. At the moment I have a bunch of rows with nothing in them but formulas. Is there a way of doing this?
 

Mikes_KM

New Member
Joined
Jun 16, 2019
Messages
16
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,628
Messages
5,597,247
Members
414,133
Latest member
lucid33

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
Top