CODEWITHSUNDEEP
powerbidax
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

DAX measure to sum only numeric values on string column with both numbers and strings

How to create DAX measure to sum up only numeric values? Suppose we have simple sample data as below. enter image description here

I thought that this code might do the job, but it failed. 

m1 = IF( MAX(Table1[category]) = "apples", SUM(Table1[units]) , BLANK() )

When I try to add this measure to the table, which has applied filters for apples and plums only, I get the error message:

enter image description here

I need something that will apply filter first, seeding out text values, then do the summing up on numeric values only.

Upvotes: 0

Views: 14205

Answers (3)

Siva
Siva

Reputation: 1

Try this measure

=
SUMX (
    FILTER ( Tablename, Table1[category] = "apples" ),
    IFERROR ( VALUE ( Table1[units] ), 0 )
)

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40244

This is a terrible way to store data, but it is possible to do what you're asking.

Try this measure, for example,

= SUMX(FILTER(Table1, Table1[type]="number"), VALUE(Table1[units]))

The VALUE function converts the string to a numeric value and we only sum over the rows where the type is "number".

Upvotes: 2

Marcus
Marcus

Reputation: 541

The main problem is that SUM will never work on a column that is defined as being text. So for example if your column was only numbers, but the column defined as text then Measure:= SUM ( table[ units] ) would always error.

The only way to really do what you want would to be having the data in separate columns by type. Then having within a calculated measure which returns based on the column.

Upvotes: 0

Related Questions