sum on distinct value based on month column

[sheenajose]

Hi,

I have a table with month,activity and value columns.

Month	Activity	Value	ValueTotal
Jan	A	9212	17282
Jan	A	9212	17282
Jan	B	5746	17282
Jan	B	5746	17282
Jan	C	1752	17282
Jan	C	1752	17282
Jan	D	572	17282
Jan	D	572	17282
Feb	A	25096	59342
Feb	A	25096	59342
Feb	B	23650	59342
Feb	B	23650	59342
Feb	C	7109	59342
Feb	C	7109	59342
Feb	D	3487	59342
Feb	D	3487	59342
Mar	A	27083	68068
Mar	A	27083	68068
Mar	B	28860	68068
Mar	B	28860	68068
Mar	C	6852	68068
Mar	C	6852	68068
Mar	D	5273	68068
Mar	D	5273	68068

I would like to have calculated column to sum on distinct Value based on month coulumn,

the result expected is of ValueTotal column

i was able to take the distinct value and sum  but unable to add filter on month before sum

ValueTotal= SUMX(SUMMARIZE('Table1','Table1'[Value]),'Table1'[value])

[CNENFRNL · Accepted solution]

Hi, @sheenajose , you might want to try this formula in a calcaulated column,

TotalDistinctValue =
SUMX (
    CALCULATETABLE (
        DISTINCT ( 'Table1'[Value] ),
        ALLEXCEPT ( 'Table1', 'Table1'[Month] )
    ),
    'Table1'[Value]
)

or equally,

TotalDistinctValue 2 =
SUMX (
    CALCULATETABLE (
        DISTINCT ( Table1[Value] ),
        FILTER ( Table1, [Month] = EARLIER ( Table1[Month] ) )
    ),
    Table1[Value]
)