split the amount value using comma separated value column dax

[Devesh]

Hi All,

I am struggling with this simple problem.

My data set:

name	amount
a	500
b	500
c,d	500
e,f,g	600
h	1000
I,j,k,l	1000

The output I want:

name	amount
a	500
b	500
c	250
d	250
e	200
f	200
g	200
h	1000
i	250
j	250
k	250
l	250

Here simple,

I am splitting the amount by the number of values I can find in the name column.

I can only use DAX as my dataset is a Calculated Table. Please help.

[MFelix]

Hi @Devesh ,

Using this post you can do the following calculation:

Word List = 
VAR SplitByCharacter = ","
VAR Table0 =
    ADDCOLUMNS (
        GENERATE (
            Unpivot,
            VAR TokenCount =
                PATHLENGTH ( SUBSTITUTE (Unpivot[name], SplitByCharacter, "|" ) )
            RETURN
                GENERATESERIES ( 1, TokenCount )
        ),
        "NameSplit", PATHITEM ( SUBSTITUTE ( Unpivot[name], SplitByCharacter, "|" ), [Value] )
    )
RETURN
  SELECTCOLUMNS(  Table0, "AMount", Unpivot[amount], "Name", [NameSplit])

PBIX file attach.

[Devesh]

Hi,

The amount does not get divided using the formula you posted. It just copies the amount into all CSV values.

[MFelix · Accepted solution]

Hi @Devesh ,

My error, did not notice the last part that you asked for the split check the formula and PBIX revised:

Word List = 
VAR SplitByCharacter = ","
VAR Table0 =
    ADDCOLUMNS (
        GENERATE (
            Unpivot,
            VAR TokenCount =
                PATHLENGTH ( SUBSTITUTE ( Unpivot[name], SplitByCharacter, "|" ) )
            RETURN
                GENERATESERIES ( 1, TokenCount )
        ),
        "NameSplit", PATHITEM ( SUBSTITUTE ( Unpivot[name], SplitByCharacter, "|" ), [Value] ),
        "Numberofwords",
            LEN ( Unpivot[name] ) - LEN ( SUBSTITUTE ( Unpivot[name], ",", "" ) ) + 1
    )
RETURN
  SELECTCOLUMNS(  Table0, "AMount", Divide(Unpivot[amount], [Numberofwords]), "Name", [NameSplit])