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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors