Forum Discussion

DizzyAy's avatar
DizzyAy
New Member
3 years ago
Solved

Create Column that averages values from each column where the column title = 'x'

I have a table similar to the below

 

IDName1.abc1.defNewColumn12.abc2.defNewColumn2
1Jack222132
1

Jill

243111

 

I want to add NewColumn1 and NewColumn2 to basically look for all columns that has a 1 as the first character of the title and then average those columns.

 

Is this possible?

 

Thanks,

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi DizzyAy 
    I guess you have a variable huge number of columns that you cannot unpivot for some reason(s). I would recommend the following solution as per the attached sample file which works only as a measure (a calculated column using the same approach is not possible).

    1.Sum = 
SUMX (
    'Table',
    VAR T1 = CALCULATETABLE ( 'Table' )
    VAR String1 = TOCSV ( T1, 1, ",", TRUE )
    VAR Items1 = SUBSTITUTE ( String1, UNICHAR ( 10 ), "|" )
    VAR T2 = SELECTCOLUMNS ( { ( 1, 2 ) }, "@Headers", PATHITEM ( Items1, 1 ), "@Details", PATHITEM ( Items1, 2 ) )
    VAR T3 =
        GENERATE ( 
            T2, 
            VAR HeaderString = [@Headers]
            VAR HeaderItems = SUBSTITUTE ( HeaderString, ",", "|" )
            VAR DetailString = [@Details]
            VAR DetailItems = SUBSTITUTE ( DetailString, ",", "|" )
            VAR Length = PATHLENGTH ( HeaderItems )
            VAR T4 = GENERATESERIES ( 1, Length, 1 )
            RETURN
                FILTER ( 
                    SELECTCOLUMNS ( 
                        T4, 
                        "@Header", PATHITEM ( HeaderItems, [Value] ),
                        "@Detail", PATHITEM ( DetailItems, [Value] ) 
                    ),
                    CONTAINSSTRING ( [@Header], "1" ) 
                )
        )
    RETURN
        SUMX ( T3, VALUE ( [@Detail] ) )
)

1 Reply

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi DizzyAy 
    I guess you have a variable huge number of columns that you cannot unpivot for some reason(s). I would recommend the following solution as per the attached sample file which works only as a measure (a calculated column using the same approach is not possible).

    1.Sum = 
SUMX (
    'Table',
    VAR T1 = CALCULATETABLE ( 'Table' )
    VAR String1 = TOCSV ( T1, 1, ",", TRUE )
    VAR Items1 = SUBSTITUTE ( String1, UNICHAR ( 10 ), "|" )
    VAR T2 = SELECTCOLUMNS ( { ( 1, 2 ) }, "@Headers", PATHITEM ( Items1, 1 ), "@Details", PATHITEM ( Items1, 2 ) )
    VAR T3 =
        GENERATE ( 
            T2, 
            VAR HeaderString = [@Headers]
            VAR HeaderItems = SUBSTITUTE ( HeaderString, ",", "|" )
            VAR DetailString = [@Details]
            VAR DetailItems = SUBSTITUTE ( DetailString, ",", "|" )
            VAR Length = PATHLENGTH ( HeaderItems )
            VAR T4 = GENERATESERIES ( 1, Length, 1 )
            RETURN
                FILTER ( 
                    SELECTCOLUMNS ( 
                        T4, 
                        "@Header", PATHITEM ( HeaderItems, [Value] ),
                        "@Detail", PATHITEM ( DetailItems, [Value] ) 
                    ),
                    CONTAINSSTRING ( [@Header], "1" ) 
                )
        )
    RETURN
        SUMX ( T3, VALUE ( [@Detail] ) )
)