Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
DizzyAy
New Member

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,

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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.png

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] ) )
)

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

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.png

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] ) )
)

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.