function Sum issue

[vjnvinod]

Hi Community,

I am getting the below error

how to fix this?

The function SUM cannot work with values of type String

my function is

Plan = SUM('Raw Data'[a. Plan Total])

[Anonymous]

Need to convert it to a number, which can be done in Power Query or DAX, but probably better in PQ. Have some sample data you can post?

[vjnvinod]

@Anonymous 

Its already in the number format

here is the sample data, take a lool at my summary tab

https://1drv.ms/u/s!AvnN5xU3DpfgbHlmRLBBS1eDp3A

[Anonymous · Accepted solution]

The issue is not with that specific column. I duplicated your table with just that column and was able to sum it just fine. I believe the issue is that this table has a lot of columns (probably better to split those up into dimension tables, but that's another discussion). I copied your table out of PBI and then added it a separate excel file to see what would happen when I tried to load it to the data model and:

So not sure where you got that data, but all of those errors are loaded into your pbix file which make your function not work. Because on its own, your function works. It is when you introduce the other filters that you get the error. I think a lot of this could be fixed by setting data types in Power Query. For example need to fix this error in the Rank Column:

If you load that table into excel--> Power Query and then load to Data Model, there will be a new table that has all the rows that have errors. 

[vjnvinod]

@Anonymous 

thanks Nick, appreciate your findings, i think error is coming because of blank fields in some of the coloumns, which is ok.

I managed to fix it, however i have a quick question, if you could share your expertise

In my dashboard after dividing by 1000 in power query my number is

167000000

& in my visual, i would like to see this as (basically in thousands)

167,000

also wanted to make sure that when my user extracts the data, he should get 167000000

Currently I am using the data label, i can change the numbers to 167,000K and my when my end user extract the data in excel, he gets the number 167000000, which is good

however my clients doesn't want to see abbreviations like "K"

[v-yulgu-msft]

Hi @vjnvinod ,

Since your original problem has been resolved, for any other question, I would suggest you create a new thread on forum so that more community members can see it and you would get correct answer more quickly.

Regards,

Yuliana Gu