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.  

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

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's avatar
FreemanZ
Icon for Super User rankSuper User
1 year ago

hi BrianNeedsHelp ,

 

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

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

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

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

      hi BrianNeedsHelp ,

       

      try like:

       

      TotalHoursByIDandMonth =
      CALCULATE(
          [TotalHoursClosed],
          ALLEXCEPT('BCP (2)', 'BCP (2)'[UniqueID0],'BCP (2)'[DateConversion]))
      • BrianNeedsHelp's avatar
        BrianNeedsHelp
        Icon for Resolver I rankResolver I
        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.