SUMMARIZE over SUMMARIZECOLUMNS

[Hoping]

I have a dataset as below:

Name	Year	OtherColumn	Points
A	2022	S1	2
A	2022	S2	2
A	2023	S1	2.5
A	2023	S2	2.5
B	2023	S1	3
B	2023	S1	3

I have used SUMMARIZECOLUMNS to summarize by   Name and Year and calculated MAX(Points)  to get the below output:

Name	Year	Max Points
A	2022	2
A	2023	2.5
B	2023	3

VAR DistinctValueTable =

SUMMARIZECOLUMNS('MyTable'[Name], 'MyTable'[Year], "Max Points", MAX('MyTable'[Points]) )

I now need to SUM Max Points for each Name. How do I do this ?

SUMMARIZE(DistinctValueTable , [Name], SUM[Max Points])

is showing red sqiggly lines below Max Points. 

Any help is appreciated. @Sahir_Maharaj  @VahidDM @Greg_Deckler @Jihwan_Kim @lbendlin @ThxAlot @tharunkumarRTK 

[Jihwan_Kim · Accepted solution]

HI,

Please check the below picture and the attached pbix file.

GROUPBY function (DAX) - DAX | Microsoft Learn

expected result table =
VAR DistinctValueTable =
    SUMMARIZECOLUMNS (
        'MyTable'[Name],
        'MyTable'[Year],
        "Max Points", MAX ( 'MyTable'[Points] )
    )
VAR ExpectedResultTable =
    GROUPBY (
        DistinctValueTable,
        MyTable[Name],
        "@expectedresult", SUMX ( CURRENTGROUP (), [Max Points] )
    )
RETURN
    ExpectedResultTable

[shafiz_p]

Hi @Hoping Try this one :

DistinctValueTable = 

VAR x = SUMMARIZECOLUMNS(
    dataset1[Name],
    dataset1[Year],
    "Max Point", MAX(dataset1[Points])
)

VAR _result =
SUMMARIZE(
    x,
    [Name  ],
    "Total Points", SUMX(
        FILTER(x, [Name] = EARLIER([Name])),
        [Max Point]
    )
)

RETURN
_result

You can also use groupby over currentgroup.

Hope this helps!!

If, Please accept it as a solution!!


Best Regards,
Shahariar Hafiz

[SachinNandanwar]

VAR DistinctValueTable = 
    SUMMARIZECOLUMNS(
        'MyTable'[Name], 
        'MyTable'[Year], 
        "Max Points", MAX('MyTable'[Points])
    )

RETURN
GROUPBY(  
DistinctValueTable,
[Name],
"Max Points", SUMX(CURRENTGROUP(), [Max Points])  
)

[lbendlin]

SUMMARIZE(DistinctValueTable , [Name], "Sum by name", SUM([Max Points]))

[Sahir_Maharaj]

Hello @Hoping,

Can you please try the following approach:

VAR DistinctValueTable = 
    SUMMARIZECOLUMNS(
        'MyTable'[Name], 
        'MyTable'[Year], 
        "Max Points", MAX('MyTable'[Points])
    )

RETURN
SUMX(
    SUMMARIZE(
        DistinctValueTable,
        [Name],
        "Total Max Points", SUMX(DistinctValueTable, [Max Points])
    ),
    [Total Max Points]
)

[Hoping]

@Sahir_Maharaj   Thank You. But I want a table and not a Measure. I tried SUMX but it is giving me incorrect results. SUMX is not summing for each Name I think instead the entire table. I get a large value repeated for each Name.

VAR DistinctValueTable =
SUMMARIZECOLUMNS(
'MyTable'[Name],
'MyTable'[Year],
"Max Points", MAX('MyTable'[Points])
)

// Giving incorrect results
VAR SumDistinctTable =
SUMMARIZE(
DistinctValueTable,
[Name],
"Sum of Distinct",
SUMX(DistinctValueTable, [Max Points])
)

EVALUATE (SumDistinctTable)