Field has text and number, need to sum all numbers

[lululjy]

Hello,

My field has numbers, text, and blanks, so when I drag it to the report it automatically shows the count, and when I right-click on it the only options are count or count distinct. How do I sum all the numbers in this column? Is it because I'm using Power BI Service only so I can't really shape the data?

Thanks

[Seth_C_Bauer]

@lululjy You are correct, in order to modify values, or create a measure to achieve your intended purpose for the field you would need to use the Power BI Desktop. Then publish to the Service.

[lululjy]

Hi,

Thanks for the response!

Is possible to create pivot table in excel and then import the pivot table into Power BI Service instead of using PowerPivot? I won't be able to use Desktop, at least not for now

Thanks!

[v-sihou-msft · Accepted solution]

@lululjy

As @Seth_C_Bauer said, you can't do modeling on Power BI Service. And in a Excel Table, one column can only have one data type, if your data field contains both number and text, it will be resolved as text, which can only be aggregated with Count. So it's not possible to sum the numbers only from that column. Since you can't use Power BI Desktop, you have to do modeling on that column or create a separated column in Excel which include those numbers only.

Regards,  

[Seth_C_Bauer]

@lululjy I would suggest testing it out, I don't import directly into the Service from Excel as a daily activity. If that is the method available to you, you would need to make any adjustments in your excel file as the same rules in the Service apply (you can't do any modeling or format changing)