Forum Discussion
BrianNeedsHelp
Resolver I
Resolver ISUMX 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 ...
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
Super User
Super UserHi 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
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
- BrianNeedsHelp
I can't use SUM. "Parameter is not the correct type".