topic Re: SUMMARIZE over SUMMARIZECOLUMNS in DAX Commands and Tips

SUMMARIZE over SUMMARIZECOLUMNS

Hoping — Sat, 14 Sep 2024 21:52:49 GMT

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

Re: SUMMARIZE over SUMMARIZECOLUMNS

Sahir_Maharaj — Sat, 14 Sep 2024 21:04:40 GMT

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

Re: SUMMARIZE over SUMMARIZECOLUMNS

Hoping — Sat, 14 Sep 2024 21:37:31 GMT

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)

Re: SUMMARIZE over SUMMARIZECOLUMNS

lbendlin — Sun, 15 Sep 2024 00:08:45 GMT

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

Re: SUMMARIZE over SUMMARIZECOLUMNS

SachinNandanwar — Sun, 15 Sep 2024 00:16:38 GMT

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

Re: SUMMARIZE over SUMMARIZECOLUMNS

shafiz_p — Sun, 15 Sep 2024 06:31:18 GMT

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

Re: SUMMARIZE over SUMMARIZECOLUMNS

Jihwan_Kim — Sun, 15 Sep 2024 09:39:54 GMT

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