+ Reply to Thread
Results 1 to 6 of 6

We can't summarize this field with Sum (power pivot)

  1. #1
    Forum Contributor
    Join Date
    06-20-2016
    Location
    Los Angeles
    MS-Off Ver
    2016
    Posts
    241

    We can't summarize this field with Sum (power pivot)

    Hello, I have a problem. One of my columns is showing as count and not as sum like I wanted. I get an error that says "We can't summarize this field with sum because it's not a supported calculation for Text data types."

    Is there anyway around it? This is Power pivot table. The column that I have problem with called Expected Sales.

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: We can't summarize this field with Sum (power pivot)

    on Daily Hours Log use M3: =IFERROR(SUM([@HOURS]*[@RATE]),0)
    then refresh PivotTable and change Expected Sales from count to sum
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Forum Contributor
    Join Date
    06-20-2016
    Location
    Los Angeles
    MS-Off Ver
    2016
    Posts
    241

    Re: We can't summarize this field with Sum (power pivot)

    Quote Originally Posted by sandy666 View Post
    on Daily Hours Log use M3: =IFERROR(SUM([@HOURS]*[@RATE]),0)
    then refresh PivotTable and change Expected Sales from count to sum
    Thank you so much! I'm lost of words.

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: We can't summarize this field with Sum (power pivot)

    You are welcome

    Have a nice day

  5. #5
    Registered User
    Join Date
    08-03-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1

    Re: We can't summarize this field with Sum (power pivot)

    Hello, first time posting here. I am having this same issue but with a Power Pivot, Pivot table. Any insights on how to correct this? I'm inserting several pivot tables for a dashboard but, for some reason, only with this one column do I get this error. I've reviewed the data over and over to no avail. Thank you! Dan

  6. #6
    Registered User
    Join Date
    11-23-2020
    Location
    Manchester, NH
    MS-Off Ver
    Office 2016
    Posts
    1

    Re: We can't summarize this field with Sum (power pivot)

    Thanks for the reply, by adding the if-error statement you removed the circumstances that didn't result in a number allowing the system to perform the calculation. So if you are running into the same issue. Add an if-error statement with a resulting value of 1 or 0 or whatever suits your data best and refresh & it should work. Hope that helps.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with VBA Code to add field to Power Pivot Report
    By rv02 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2016, 10:47 AM
  2. Error in power pivot calculated field
    By stephme55 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-27-2016, 01:42 PM
  3. Can't summarize calculated field in a Pivot Table
    By Pryach in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-18-2015, 03:49 PM
  4. [SOLVED] Power Pivot Summarize Value By Category From Total
    By goss in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-24-2014, 05:52 AM
  5. Replies: 1
    Last Post: 04-21-2014, 12:59 PM
  6. Pivot Table FRom Power Pivot Returns Sum Of Entire Field
    By goss in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-26-2013, 07:34 PM
  7. [SOLVED] pivot table field summarize default
    By Pam Deshazier, SRHS in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-22-2006, 08:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1