power bi: split column values by character and calculate sum

0
votes

I have a table in which numeric values are there, but in some rows may contains values separated by comma. Since comma is in there so the data type of the column will be text. I want to calculate sum of this column.

Here is my table-

Id  Values
A   12
B   13
C   15
D   13,11,12
E   16

I want my sum to be - 12+13+15+13+11+12+16

Since the datatype of this column will be text so can the sum be calculated like I want or I have to do something like this-

      Id  Values
        A   12
        B   13
        C   15
        D   13
        D   11
        D   12
        E   16
1
powerbidax

1 Answers

4
votes

You can split the column by delimiter:

split

And split it into rows instead of columns:

option

The result will be as what you expected:

result

Then you can sum it as usual with DAX:

Sum = SUM(Data[Values])

sum