Sum After Grouping

[Shawn_Eary]

DAX seriously confuses me.  What is the DAX code for TotalMilesMeasure in the following table? 
 

Name	Miles	TotalMilesMeasure
Jeff	4	42
Johnny	4	4
Jeff	8	42
John	10	30
John	20	30
Lisa	24	64
Jeff	30	42
Lisa	40	64

[camargos88 · Accepted solution]

Hi @Shawn_Eary ,

Try this code for measure:

Total = CALCULATE(SUM('Table'[Miles]); ALLEXCEPT('Table';'Table'[Name]))

Ricardo

[Shawn_Eary]

@camargos88- Your solution works perfectly and this thread is solved now, but shouldn't this work also?

TotalMilesMeasure = SUMMARIZE(
    'Table',
    'Table'[Name],
    "Total Miles Grouped by Athlete",
    SUM ( 'Table'[Miles] )
)

It's my attempt to use the technique mentioned by Russo at
https://www.sqlbi.com/articles/from-sql-to-dax-grouping-data/
unfortunately, it keeps giving me a "The expression refers to multiple columns.  Multiple columns cannot be converted to a scalar value." error.

I really appreicate you exposing me to the ALLEXCEPT function but I'm still struggling to find understanding.

[camargos88]

@Shawn_Eary ,

Summarize won't work because measures expect a scalar value, you can use variables with summarize and return it with sumx, like:

var _tbl = SUMMARIZE(
'Table',
'Table'[Name],
"Total Miles Grouped by Athlete",
SUM ( 'Table'[Miles] )
)

return sumx(_tbl; Total Miles Grouped by Athlete)

However, it won't also give your desired result, you need to ignore everything on the table except the name, that when ALLEXCEPT works.

Ricardo