Forum Discussion

Advocate V

6 years ago

Solved

Sum After Grouping

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
6 years ago
Hi Shawn_Eary ,

Try this code for measure:

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

Ricardo

3 Replies

camargos88
Community Champion
6 years ago
Hi Shawn_Eary ,

Try this code for measure:

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

Ricardo
- Shawn_Eary
  Advocate V
  6 years ago
  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
    Community Champion
    6 years ago
    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

Related content