topic Re: Sum Measure with Filter in DAX Commands and Tips

Sum Measure with Filter

dolevh — Sun, 26 Dec 2021 13:51:43 GMT

Hi,

I'm looking for Dax that show me the Total Count
if (FirstDayofMonth = FirstDayofMonth and AreaID = AreaID and CityCode = CityCode and HoodID = HoodID).

My name of this table is: NadRe

FirstDayofMonth	CustID	AreaID	CityCode	HoodID	Count
1/1/21	11111	1	200	100	10
1/1/21	22222	1	200	100	20
1/1/21	33333	1	200	100	30
1/1/21	77777	2	400	10	100
1/1/21	66666	2	400	10	200
2/1/21	11111	1	200	100	1000
2/1/21	22222	1	200	100	2000
2/1/21	33333	1	200	100	3000
2/1/21	77777	2	400	10	10,000
2/1/21	66666	2	400	10	20,000

This is the result:

FirstDayofMonth	HoodID	Count
1/1/21	100	60
1/1/21	10	300
2/1/21	100	6,000
2/1/21	10	30,000

Thanks All !

Re: Sum Measure with Filter

ValtteriN — Sun, 26 Dec 2021 16:28:04 GMT

Hi,

This shoudl do what you want:

Table 2 = SUMMARIZE('Summarize (2)','Summarize (2)'[FirstDayofMonth ],'Summarize (2)'[ HoodID],"Count",SUM('Summarize (2)'[ Count]))

I hope this helps and if it does consider accepting this as a solution!

Re: Sum Measure with Filter

smpa01 — Sun, 26 Dec 2021 16:38:10 GMT

dolevh you can simply use this measure

Measure = CALCULATE ( SUM ( 'Table'[Count] ), ALLEXCEPT ( 'Table', 'Table'[FirstDayofMonth], 'Table'[HoodID] ) )

Re: Sum Measure with Filter

Jihwan_Kim — Mon, 27 Dec 2021 15:24:55 GMT

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table, and it uses SUMMARIZECOLUMNS function.

Result Table =

SUMMARIZECOLUMNS (

NadRe[FirstDayofMonth],

NadRe[HoodID],

"Count", SUM ( NadRe[Count] )

)