Re: SUM Text and Numeric

Anonymous · ‎06-06-2019

Hi,

Wondering if you can help.

I have data which looks like the following, below dummy data

Name Of Appeal	Code	Volume	Status
TV	FIN	10	Success
Media	FIN	5	Failed
Press	FIN	10	Success
Email	XXX	OK	P

I would like to sum up Value where the code is FIN, but The volume field is stored as text because obviously there are string values. I tried to create a filter where by it would include everything with FIN and then convert the Volume Column using the Value, but struggling. I would also need to make sure I only sum the rows with a value of 'success'.

I tried to also do the IFNUMBER at Query Level, but because the entire column is text, it just outputs every row as text.

Any guidance would be appreciated.

Arjunarao · ‎06-06-2019

Hi Figo7

Can you show us your expected output?

HotChilli · ‎06-06-2019

The pattern to use is

Measure = SUMX(FILTER(Table2, Table2[Code] = "FIN") , VALUE(Table2[Volume]) )

so you're using VALUE for each row in your filtered table.

I'll let you add the 'success' part

Anonymous · ‎06-06-2019

I had something similar before & have the same problem. The visual cannot be displayed and the error is because the VALUE function cannot convert the string 'OK' in the colum called Volume.

Cannot convert value 'OK' of type Text to type Number

@HotChilli

@HotChilli wrote:
The pattern to use is
Measure = SUMX(FILTER(Table2, Table2[Code] = "FIN") , VALUE(Table2[Volume]) )
so you're using VALUE for each row in your filtered table.
I'll let you add the 'success' part

HotChilli · ‎06-06-2019

You need to debug it at your side. I used your data (thanks for providing this, many people don't)

If you link your pbix, I'll have a look

Anonymous · ‎06-06-2019

Hi,

The data was an example as I'm working personal identifiable data. I'll debug and explore.

Thanks for your speed reply.

@HotChilli wrote:
You need to debug it at your side. I used your data (thanks for providing this, many people don't)
If you link your pbix, I'll have a look