Summarize

[Anonymus_18]

I have provided the sample table.I need to summarize the numbers there is same sports rows with places interchanged.so I need to add them in a single row(teqball,basketball |60).

sports	number
teqball	6
teqball,basketball	29
basketball	15
soccer	65
soccer,teqball	50
basketball,teqball	31

[Ashish_Mathur]

Hi,

This should ideally be done in the Query Editor.  If you OK with my approach, then post back.

[Anonymous · Accepted solution]

Hi @Anonymus_18 

You can refer to the follwing calculated table.

Table 2 =
VAR add1 =
    ADDCOLUMNS (
        'Table',
        "Type1",
            VAR a =
                SEARCH ( ",", 'Table'[sports], 1, BLANK () )
            RETURN
                IF ( a <> BLANK (), LEFT ( [sports], a - 1 ), [sports] )
    )
VAR add2 =
    ADDCOLUMNS (
        add1,
        "Type2",
            VAR a =
                SEARCH ( ",", 'Table'[sports], 1, BLANK () )
            VAR b =
                IF ( a <> BLANK (), MID ( [sports], a + 1, LEN ( [sports] ) - a ), [sports] )
            RETURN
                IF ( b = [Type1], BLANK (), b )
    )
VAR add3 =
    ADDCOLUMNS (
        add2,
        "Index",
            IF (
                [Type2] = BLANK (),
                1,
                RANKX (
                    FILTER (
                        'Table',
                        CONTAINSSTRING ( [sports], EARLIER ( [Type1] ) )
                            && CONTAINSSTRING ( [sports], EARLIER ( [Type2] ) )
                    ),
                    [number],
                    ,
                    ASC
                )
            ),
        "Sum",
            IF (
                [Type2] = BLANK (),
                [number],
                CALCULATE (
                    SUM ( 'Table'[number] ),
                    FILTER (
                        'Table',
                        CONTAINSSTRING ( [sports], EARLIER ( [Type1] ) )
                            && CONTAINSSTRING ( [sports], EARLIER ( [Type2] ) )
                    )
                )
            )
    )
RETURN
    SUMMARIZE ( FILTER ( add3, [Index] = 1 ), [sports], [Sum] )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.