Monthly Sum as a Measure

[Jorgast]

Hello BI Friends,

I need your help. I am trying to get the monthly sum as a measure for each month. The Category table is connected to a date table.

I have tried to calculate using Allselected, allexcept, and filter. None of these seem to work. I am not sure what I am missing.

Thanks in Advance.

MMM	Category	Count
May-21		15070
May-21	Contract	31029
May-21	Commercial	117946
May-21	Industrial	60420
May-21	Pharma	131
May-21	Residential	417034

MMM	Category	Count	Desired Results
May-21		15070	641630
May-21	Contract	31029	641630
May-21	Commercial	117946	641630
May-21	Industrial	60420	641630
May-21	Pharma	131	641630
May-21	Residential	417034	641630

MMM	Category	Count
Jun-21		16496
Jun-21	Contract	36503
Jun-21	Commercial	136074
Jun-21	Industrial	68410
Jun-21	Pharma	163
Jun-21	Residential	497232

MMM	Category	Count	Desired Results
Jun-21		16496	754878
Jun-21	Contract	36503	754878
Jun-21	Commercial	136074	754878
Jun-21	Industrial	68410	754878
Jun-21	Pharma	163	754878
Jun-21	Residential	497232	754878

[DataInsights · Accepted solution]

@Jorgast,

Try this measure:

Monthly Sum = 
CALCULATE (
    SUM ( Category[Count] ),
    ALL ( Category ),
    VALUES ( DimDate[Month Year] )
)

The Category table has a relationship with DimDate:

The visual uses DimDate[Month Year]: