topic Re: SUMX Group By Multiple Categories in DAX Commands and Tips

SUMX Group By Multiple Categories

BrianNeedsHelp — Wed, 20 Nov 2024 01:59:44 GMT

I have this table, which shows the correct results in Total By ID and Month.

UniqueID0	DateClosed	TotalHoursClosed	Total By ID and Month
1	9/30/2024	8	8
1	10/9/2024	8	12
1	10/10/2024	4	12
2	10/9/2024	8	16
2	10/9/2024	8	16
3	9/30/2024	4	4
3	10/9/2024	4	4

If I use this I get the equivilant of Total Hours Closed Column.

TotalHoursByIDandMonth = Calculate(SUMX(VALUES('BCP (2)'[UniqueID0]),[TotalHoursClosed]),GROUPBY('BCP (2)','BCP (2)'[UniqueID0],'BCP (2)'[DateConversion]))

Even though it's not making sense because I've read that ALLEXCEPT just removes filters, and I don't have any filters, but it's closer to what I need-it sums by UniqueID0 but doesn't take into consideration the date, so the first 3 rows e.g. equals 20:

TotalHoursByIDandMonth = Calculate(SUMX(VALUES('BCP (2)'[UniqueID0]),[TotalHoursClosed]),ALLEXCEPT('BCP (2)','BCP (2)'[UniqueID0],'BCP (2)'[DateConversion]))

[DateConversion] is a Calculated Column

DateConversion = EOMONTH('BCP (2)'[DateClosed],-1)+1

How would I sum this grouping by UniqueID0 and DateConversion(FirstOfMonth)?

Re: SUMX Group By Multiple Categories

FreemanZ — Wed, 20 Nov 2024 02:14:04 GMT

Hi BrianNeedsHelp ，

try like:

column = VAR _id = [UniqueID0] VAR _ym = EOMONTH([DateClosed], 0) VAR _result = SUMX( FILTER( bcp, bcp[UniqueID0] = _id && EOMONTH(BCP[DateClosed], 0) = _ym ), bcp[TotalHoursClosed] ) RETURN _result

it works like:

as a general rule, try to avoid use CALCULATE in creating calculated columns.

Re: SUMX Group By Multiple Categories

FreemanZ — Wed, 20 Nov 2024 02:18:36 GMT

With CALCULATE, try like:

column 2 = CALCULATE( SUM(BCP[TotalHoursClosed]), ALLEXCEPT(BCP, BCP[UniqueID0], BCP[DateClosed]) )

SUMX(VALUES() ) is more for other more complicated occassion, like calculating with higher-than-record level of granularities.

Re: SUMX Group By Multiple Categories

BrianNeedsHelp — Wed, 20 Nov 2024 02:59:40 GMT

I was trying to do this in a measure, so when I use this I get sum totals by ID, but doesn't take into consideration the date.

TotalHoursByIDandMonth =

CALCULATE(

SUMX('BCP (2)',[TotalHoursClosed]),

ALLEXCEPT('BCP (2)', 'BCP (2)'[UniqueID0],'BCP (2)'[DateConversion]))

I tried using the column, but I get an error "A circular reference was detected" Any idea on that?

Re: SUMX Group By Multiple Categories

FreemanZ — Wed, 20 Nov 2024 03:44:26 GMT

hi BrianNeedsHelp ,

you are using CALCULATE+SUMX, using CALCULATE+SUM instead.

Re: SUMX Group By Multiple Categories

BrianNeedsHelp — Wed, 20 Nov 2024 03:49:36 GMT

I can't use SUM. "Parameter is not the correct type".

Re: SUMX Group By Multiple Categories

Kedar_Pande — Wed, 20 Nov 2024 05:06:08 GMT

BrianNeedsHelp

Create the Measure:

TotalHoursByIDandMonth = 
CALCULATE(
SUM('BCP (2)'[TotalHoursClosed]),
ALLEXCEPT('BCP (2)', 'BCP (2)'[UniqueID0], 'BCP (2)'[DateConversion])
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Re: SUMX Group By Multiple Categories

BrianNeedsHelp — Wed, 20 Nov 2024 05:20:10 GMT

I did this, but I figured out I could tack on .Month i.e. [DateConversion].Month. Thought that would solve it but it still sums the total by UniqueID0 without considering the month. I think this doesn't like me.

TotalHoursByIDandMonth =

CALCULATE(

SUMX('BCP (2)',[TotalHoursClosed]),

ALLEXCEPT('BCP (2)', 'BCP (2)'[UniqueID0],'BCP (2)'[DateConversion].[Month]))

Re: SUMX Group By Multiple Categories

FreemanZ — Wed, 20 Nov 2024 06:01:42 GMT

hi BrianNeedsHelp ,

try like:

TotalHoursByIDandMonth =

CALCULATE(

[TotalHoursClosed],

ALLEXCEPT('BCP (2)', 'BCP (2)'[UniqueID0],'BCP (2)'[DateConversion]))

Re: SUMX Group By Multiple Categories

BrianNeedsHelp — Wed, 20 Nov 2024 06:15:47 GMT

I've tried tha numerous times. It doesn't recognize the date portion. So I created another column by using Month('BCP (2)'[DateClosed]). And then tried pretty much the same thing. I tried to see what it would do if I just used the Date in the AllEXCEPT and it doesn't distinguish the months-it just sums all the hours...

TotalHoursByIDandMonth =

CALCULATE(

SUMX('BCP (2)',[TotalHoursClosed]),

ALLEXCEPT('BCP (2)', 'BCP (2)'[UniqueID0],'BCP (2)'[ColumnMonthExtract]))

Re: SUMX Group By Multiple Categories

BrianNeedsHelp — Wed, 20 Nov 2024 20:59:44 GMT

FreemanZ Kedar_Pande I did it! This works!

TotalHoursByIDandMonth2 = CALCULATE( SUMX('BCP (2)',[TotalHoursClosed]), REMOVEFILTERS('BCP (2)'),VALUES('BCP (2)'[UniqueID0]),VALUES('BCP (2)'[ColumnMonthExtract]))

Could someone mark this as a solution and give a kudos, since I answered my own question? Please see Using ALLEXCEPT versus ALL and VALUES - SQLBI for reference. Hope it helps someone.