Forum Discussion

BrianNeedsHelp's avatar
BrianNeedsHelp
Icon for Resolver I rankResolver I
1 year ago
Solved

SUMX Group By Multiple Categories

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 ...
ALLEXCEPT
CALCULATE
groupby
  • BrianNeedsHelp's avatar
    BrianNeedsHelp
    1 year ago

    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.  

Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
1 year ago

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's avatar
BrianNeedsHelp
Icon for Resolver I rankResolver I
1 year ago

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