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.  

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

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.