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 !

 

[Dryver14]

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]

@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.

 

[Dryver14]

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]

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.

 

[Dryver14]

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

 

[Mikes_KM]

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]

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]

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]

https://drive.google.com/file/d/10zzOrZW4IZT1BcTU3MmnRyBS8SfpyKgs/view?usp=sharing

https://drive.google.com/file/d/1XD39fbNyW4ciHl1qw6bVciyJ-sUdCpvW/view?usp=sharing

Because of these blanks, I have to manually select the data range I want to create the pivot table.

1. Will my pivot table range expand as I fill these blank rows with more data?
2. Is there a way to get rid of these blank rows yet have the formulas continue down as I add more data.?