Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
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
  • Anonymous's avatar
    Anonymous
    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.  

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

hi Anonymous ,

 

try like:

 

TotalHoursByIDandMonth =
CALCULATE(
    [TotalHoursClosed],
    ALLEXCEPT('BCP (2)', 'BCP (2)'[UniqueID0],'BCP (2)'[DateConversion]))
Anonymous's avatar
Anonymous
Not applicable
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.