SUMX Group By Multiple Categories

[BrianNeedsHelp]

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)?  

[Kedar_Pande]

@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

[BrianNeedsHelp]

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]))

[FreemanZ]

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. 

[FreemanZ]

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. 

[BrianNeedsHelp]

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?  

[FreemanZ]

hi @BrianNeedsHelp ,

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

[BrianNeedsHelp]

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

[FreemanZ]

hi @BrianNeedsHelp ,

try like:

TotalHoursByIDandMonth =

CALCULATE(

    [TotalHoursClosed],

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

[BrianNeedsHelp · Accepted solution]

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

[BrianNeedsHelp]

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]))