Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shawn_Eary
Advocate IV
Advocate IV

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 

 
 
 

1 ACCEPTED SOLUTION
camargos88
Community Champion
Community Champion

Hi @Shawn_Eary ,

 

Try this code for measure:

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

3 REPLIES 3
camargos88
Community Champion
Community Champion

Hi @Shawn_Eary ,

 

Try this code for measure:

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@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.

@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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors