Pivot table sum value issue

[leojez]

Hello,

In my source data table in cell AE9, I have the following formula:

=IF(AD9<>"",IFERROR((J9-AD9)*K9*100,""),"")

This returns any error as a blank and also returns a blank if cell AD9 is blank (I know complicated! But it works well in that table.)

The issue is, when inserting pivot tables I can't get this to show as a sum value ... I get the following error when tying to change to "Sum" in the Value Settings:
"We cannot summarize this field with Sum because it's not a supported calculation for Text data types."

I've tried changing the last "" in the formula to a "0" but this results in the same error.

Any ideas how to fix? Many thanks in advance!

 

[Alex Blakenburg]

I am pretty sure you are using Power Pivot and it is pulling data from the data model.
Are you also using Power Query ?
Either in Power Pivot > Manage. Highlight the value column and make sure the data type is a numeric data type. OR go into the underlying Power Query and do it there.

 

[leojez]

I am pretty sure you are using Power Pivot and it is pulling data from the data model.
Are you also using Power Query ?
Either in Power Pivot > Manage. Highlight the value column and make sure the data type is a numeric data type. OR go into the underlying Power Query and do it there.

View attachment 62821

Thanks for your reply!

I don't have Power Pivot activated and I've also never used Power Query. I forgot to mention though that when I inserting the pivot table, I did so by adding the data to a data model (so I can get some text fields to display as text). I can now see that this is causing the issue - I've created another pivot without data model this and the formula is returned correctly. However, is there any way I can get the issue resolved in my data model pivot table?

Thanks!

 

[RoryA]

Return 0 not "0" so that it is a number not text. Or use a calculated column in the data model table so that you can return BLANK()

 

[leojez]

Return 0 not "0" so that it is a number not text. Or use a calculated column in the data model table so that you can return BLANK()

Thanks so much!