cancel
Showing results for
Did you mean:
abhay03 Regular Visitor

## sum of values in a Text type field

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)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Vvelarde Super Contributor

## Re: sum of values in a Text type field

@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

Lima - Peru

Proud to be a Datanaut!

2 REPLIES 2
Highlighted
Vvelarde Super Contributor

## Re: sum of values in a Text type field

@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

Lima - Peru

Proud to be a Datanaut!

abhay03 Regular Visitor

## Re: sum of values in a Text type field

Thanks @Vvelarde,

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

Announcements #### Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future. Learn the answers to some of the questions asked during the Amanda Triple A event. #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.  Top Kudoed Authors
Users Online
Currently online: 254 members 2,683 guests
Recent signins:
• vicente-iii • zenisekd • SMF_DATA • minwu • Abby789 • Markus_74 • basu • mjbear_11 • RRohit • Raaverok • ariefindra • TYTY • sakshibhala • okaplan 