Forum Discussion

BrianNeedsHelp's avatar
BrianNeedsHelp
Resolver 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
19/30/202488
110/9/2024812
110/10/2024412
210/9/2024816
210/9/2024816
39/30/202444
310/9/202444

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


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.  

10 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    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's avatar
      BrianNeedsHelp
      Resolver 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
    Super 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. 

  • Kedar_Pande's avatar
    Kedar_Pande
    Super 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
      Resolver 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]))