Forum Discussion

purple_ninja's avatar
purple_ninja
Regular Visitor
9 years ago

SUM function cannot work with values of type String error

Hi

 

I'm new to Power BI and trying to create some dashboards that show the sum of costs by category. I've followed the steps below but I cannot create a chart as I keep getting the error 'Function SUM takes na argument that evaluates to numbers or dates and cannot work with values of type string'.

 

In power query

1) pulled data from source,

2) converted all number columns to whole number

3) removed any error or null values with 0

4) loaded to workbook and checked fields - those that have come through as 'general' I have changed in the work book to number/date accordingly (why does it change it anyway??)

 

In Power BI desktop

5) Loaded data and created pie chart showing the categories

6) created a measure to calculate the total cost

7) attemped to create a bar chart to show costs by category.....

8) Error message

 

In an ideal world I'd like to build the power query into the Power BI dashboard so I don't have to keep refreshing a second file - is there an eay way to load the connection?

5 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi purple_ninja,

     

    Please make sure the data you loaded to PowerBI desktop is in correct format, especially the cost column which should be number type.

     

    If you have any question, please feel free to ask.

     

    Best regards,
    Yuliana Gu

    • SofG's avatar
      SofG
      Frequent Visitor
      7 years ago

      Hi,

       

      I have the same problem except that the data type is already "whole number".

       

      The error message says: Calculation error in measure 'XX': The function SUNX cannot work with values of type string.

       

      Is there another solution to this?

       

      Thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks! This solved my problem

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    When you say you're loading them in Power Query, do you mean you're loading them in Excel using Power Query and then using Power BI Desktop to load from the Excel file? You can skip that. Power BI's query editor is the same as Excel's Power Query. You can just build your query in Power BI. In fact you could open the advanced editor in the Excel Power Query window and just copy the entire query as text into a blank query in Power BI if you don't want to rebuild your query from scratch.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, 

    I can't create a total column with the SUM function. I have the same problem, Power BI desktop tells me that it can't display the value through a visual.

    Can you help me?