Forum Discussion
SUM Text and Numeric
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 |
| 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.
6 Replies
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
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 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
