Pivot table with data model wont sum the numbers - say its text but there are only numbers

Hi all,

I have an issue with the pivot table and using the data model.

I need to use the data model for the distinct count and but it wont let me sum the columns and says it only recognises the numbers as text even though there are only numbers in the columns.

I am trying to convert all the columns in the pivot table in 0.REPORT sheet to sum with the exception of the No. of Orders column but the following error keeps coming up:

"We can't summarise this field with Sum because its not a supported calculation for text data types"

FOR CONTEXT: This pivot table pulls the data from the Purchase Order Sheet which pulls the original data from the Analysis sheet. The Purchase order sheet is there as a helper so all the product items can be listed on seperate rows rather than all the same row which is what it is on the Analysis sheet.

Can someone please help - this is the final hurdle to completing my spreadsheet which I have worked a month on with great help from people on the forum.

Many thanks

The problem is the formula you used: IFERROR(.....,"") - the double-quotation creates texts.

Hi I remove the double quotation on all those columns but the problem still remains the same. Can you share the solution on the spreadsheet example I sent so I can see what you have done that it works because Ive tried it and it doesnt. Thanks

Column AA of Purchase Order sheet - change IFERROR(......,"") to IFERROR(......,0), then refresh your PivotTable.

You can now SUM column C of the PivotTable.

brilliant - just releaised on row 3000 the formula doesnt go all the way to the bottom which is why it didnt work the first time as well many thanks it works now