Forum Discussion

Hoping's avatar
Hoping
Icon for Helper III rankHelper III
2 years ago
Solved

SUMMARIZE over SUMMARIZECOLUMNS

I have a dataset as below:

 

Name  Year  OtherColumn  Points  
A2022S12
A2022S22
A2023S12.5
A2023S22.5
B2023S13
B2023S13

 

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

 

Name  Year  Max Points  
A20222
A20232.5
B20233

 

 

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 

 

"Need Help"
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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

     

6 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

  • SachinNandanwar's avatar
    SachinNandanwar
    Icon for Impactful Individual rankImpactful Individual
    2 years ago
    VAR DistinctValueTable = 
    SUMMARIZECOLUMNS(
        'MyTable'[Name], 
        'MyTable'[Year], 
        "Max Points", MAX('MyTable'[Points])
    )

RETURN
GROUPBY(  
DistinctValueTable,
[Name],
"Max Points", SUMX(CURRENTGROUP(), [Max Points])  
)
  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    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

     

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Hoping
      Icon for Helper III rankHelper III
      2 years ago

      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)
  • shafiz_p's avatar
    shafiz_p
    Icon for Super User rankSuper User
    2 years ago

    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