Using Measures: The function SUM cannot work with values of type String

[Caiz]

Hi there,

I have two excel tables I've imported with the same columns to show as a table for each within Power BI. I have replicated code for a Measure that will display the variance between two columns values. It works for my Revenue table, however doesn't work for NEW Expenditure.

I get the error "The function SUM cannot work with values of type String". 

Revenue:

Month Variance = IFERROR(
  (SUM(Revenue[Actual Month]) - SUM(Revenue[Budget Month]))/SUM(Revenue[Budget Month]), 0)

New Expenditure:

Month Variance = IFERROR(
  (SUM('NEW Expenditure'[Actual Month]) - SUM('NEW Expenditure'[Budget Month]))/SUM('NEW Expenditure'[Budget Month]), 0)

I have also observed that with the Expenditure measure, I can only reference the table with quotation marks whereas the other one doesn't require the quotation marks which is odd.

Could someone please help me with this?

[dedelman_clng · Accepted solution]

Hi @Caiz -

Check the data types in "Transform Data" for your NEW Expenditure table. Likely one or more of the columns is being treated as "Text" or "Any" because some value prevented it from being recognized as a number all the way down.

The ' ' around NEW Expenditure is because of the space in the table name. If you named it New_Expenditure or NewExpenditure it would not have the ' '.

Hope this helps

David

[mahoneypat]

Sounds like one of your columns is type text instead of numeric.  Check/fix that in the query editor.

Also, if your two tables have the same columns, you should likely Append them together in the query editor.  Add a fixed column before the append that you can use in a slicer, visual, etc..  You would then disable load on the source tables, so you only have the one table in your model (vs. those three).

Pat