Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
purple_ninja
Regular Visitor

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

😎 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 5
Anonymous
Not applicable

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?

v-yulgu-msft
Employee
Employee

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.

 

3.PNG

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

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks! This solved my problem

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!

KHorseman
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.