How to calculate the average size of size list text in Direct Query mode

[Anonymous]

In short, Now I have a text column like                     //maybe more than 3 size pairs

"24x24 36x36 64x80"

Now I have to calculate the average width (24 + 36 +64) / 3   

further, I need to calculate how many square in the above text. (2)

Notice: I use direct query mode now.          Use DAX or power query are both OK.

[FreemanZ]

hi @Anonymous 

do you have more sample data?

the digit could be extracted with LEFT, RIGHT functions with DAX and PQ. 

[Anonymous]

we can make it manually,

[size_list]

"12x23 20x20 30x30 512x512"

"1024x768 1920x1080"

"2080x3070 1x1 2x2 3x4 4x5 5x6 6x7 7x8"

like these

[FreemanZ]

hi @Anonymous ,

you can try with Power Query. check what is attached. 

[Anonymous]

The Table.SplitColumn is not supported in direct query mode.

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a calculated column as below to get it, please find the details in the attachment.

Average width = 
VAR _replace1 =
    SUBSTITUTE ( 'Table'[size_list], " ", "|" )
VAR SplitBySpace =
    PATHLENGTH ( _replace1 )
VAR _replace2 =
    SUBSTITUTE ( _replace1, "x", "|" )
VAR SplitBySpace2 =
    PATHLENGTH ( _replace2 )
VAR Mylen =
    LEN ( _replace2 )
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, SplitBySpace2 ),
        "mylist", VALUE ( PATHITEM ( _replace2, [Value] ) )
    )
VAR Result =
    DIVIDE (
        SUMX ( FILTER ( mytable, MOD ( [Value], 2 ) = 1 ), [mylist] ),
        SplitBySpace,
        0
    )
RETURN
    Result

Best Regards