sum of values in a Text type field

[abhay03]

I have a table which has some string and some numeric values. Becuase of string values the type of the field is Text.

Series	Files	Value
I	A	abc
I	A	abc
I	B	2
I	B	2
I	C	3
I	C	3
II	A	abc
II	B	5
II	B	5
II	C	3
II	C	3

I want to create a measure that filters out text fields and calculates the sum of Distinct values by Files group by Series for numeric values. So here the expected outcome would be:

Series	Count	Explanation
I	5	Distinct of B + Distinct of C (2+3)
II	8	Distinct of B + Distinct of C (5+3)

[Vvelarde · Accepted solution]

@abhay03

Hi, try this:

To a better explanation i create columns :

1: Two calculated Columns:

IsText = IF(ISERROR(VALUE(Table1[Value])),"Text","Number")

ValuetoNumber = IF(Table1[IsText]="Number",VALUE(Table1[Value]))

2. A measure to SUM

Measure =
SUMX (
    SUMMARIZE (
        'Table1',
        'Table1'[Series],
        'Table1'[Files],
        "ValueX", AVERAGE ( Table1[ValuetoNumber] )
    ),
    [ValueX]
)

Regards

Victor

Lima - Peru

[abhay03]

Thanks @Vvelarde,

you are just missing a comma after SUMMARIZE function in the measure. Your solution worked perfectly for me.