Sum Comma seperated Values using a calculated column

[Anonymous]

Dear All,

I have a validated Data Model to which i can bring only little change to it. No major Power Query or Data modeling possible.

I have imported through the model a column containing integer values seperated by ";" which represent the rejected quantity per batch.

1 row in my fact table is complaint record

Each complaint may involve several batches (up to 30 max) , meaning this may vary complaint to complaint.

I would like to create a simple calculated column retreiving each numerical values and sum the result.

I tried the SUBTITUTE to replace the ";" with "+" to input this in an EVALUATE function like you would do in VBA, but it does not work (the SUBTITUTE part does, but then DAX does not intreprate this as a formula to evaluate it)

I do not know how to do this or how to temporarly store those values in an Array variable to then sum it.

I'm looking for options and idea to unlock this.

looking forward for your help

[v-rzhou-msft]

Hi @Anonymous  

parry2k has shown you the way by dax. You can achieve your goal by M Query in Power Query Editor as well.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YGTBgYWBgbWEAosYmANg0YmBkApY1NzA0tTmJZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Refused Quantity(Batch by Batch)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Refused Quantity(Batch by Batch)", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Refused Quantity(Batch by Batch)", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Refused Quantity(Batch by Batch).1", "Refused Quantity(Batch by Batch).2", "Refused Quantity(Batch by Batch).3", "Refused Quantity(Batch by Batch).4", "Refused Quantity(Batch by Batch).5", "Refused Quantity(Batch by Batch).6", "Refused Quantity(Batch by Batch).7"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Refused Quantity(Batch by Batch).1", Int64.Type}, {"Refused Quantity(Batch by Batch).2", Int64.Type}, {"Refused Quantity(Batch by Batch).3", Int64.Type}, {"Refused Quantity(Batch by Batch).4", Int64.Type}, {"Refused Quantity(Batch by Batch).5", Int64.Type}, {"Refused Quantity(Batch by Batch).6", Int64.Type}, {"Refused Quantity(Batch by Batch).7", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", {"Refused Quantity(Batch by Batch).7", "Refused Quantity(Batch by Batch).6", "Refused Quantity(Batch by Batch).5", "Refused Quantity(Batch by Batch).4", "Refused Quantity(Batch by Batch).3", "Refused Quantity(Batch by Batch).2", "Refused Quantity(Batch by Batch).1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Index"}, {{"Refused Quantity(Batch by Batch)", each List.Sum([Value]), type number}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Index"})
in
    #"Removed Columns1"

The original Table:

Result:

You can download the pbix file from this link: Sum Comma seperated Values using a calculated column

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[parry2k]

@Anonymous here is the measure that will work

Measure = 
VAR col = MAX ( 'Table'[Column1] )
VAR mymeasure =
    SUBSTITUTE ( col, ",", "|" )
VAR Mylen =
    PATHLENGTH ( mymeasure )
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, mylen ),
        "mylist", VALUE ( PATHITEM ( mymeasure, [Value] ) )
    )

RETURN
    SUMX ( mytable, VALUE ( [mylist] ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k 

Many thanks for the proposed approach, exactly what I was looking for. Even not being as advanced user as you are, I understand the logic you proposed.

few comments: 

I need this to work in a row context environment in order to assess for every row the sum of each rejected quantities.

>> if I plug this DAX in a calculated Column to be assessed in a row context, I get always the same value.

can we get to the desire result? (Note the Table'[Column1] may contain blank values if important to know)

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[amitchandak]

@Anonymous , Not very clear, refer split can help

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/