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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.