Distinct sum issue

[Anonymous]

I need to sum the distinct values in the headcount column and get the sum in  every row in another column , this is some data to work on. i'm using 

VAR myheadcount =

SUMMARIZE( Table, Table[Headcount],Table[Year])

RETURN

SUMX(myheadcount, Table[Headcount])

this returns the distinct sum of all months in the year for me , but i need the sum according to months.

Is there any easy way to achieve this? The data shown is for just one month.

Amount	Headcount	Distinct sum headcount
11,970	7	27
6,152	7	27
6,552	7	27
17,500	7	27
13,678	6	27
18,432	6	27
5,161	6	27
7900	6	27
97400	1	27
2666	1	27
8989	1	27
3,168	1	27
1,047	13	27
2899	13	27
19,171	13	27
6,773	13	27

[MFelix]

Hi @Anonymous ,

You can try the following measure:

Measure =
VAR temp_table =
    FILTER (
        SUMMARIZE ( ALL ( 'Table' ); 'Table'[DAte]; 'Table'[Headcount] );
        'Table'[DAte] = SELECTEDVALUE ( 'Table'[DAte] )
    )
RETURN
    CALCULATE ( SUMX ( temp_table; 'Table'[Headcount] ) )

Be aware that you don't refer if the date is based I have made an example with all dates being the same for the same period, but you can change the filtering to add the month / year if you have those columns instead of the date.

check PBIX file attach.

[Anonymous]

@MFelix ,

 

I just replaced date with month in my table. It's not summing up correctly

[MFelix · Accepted solution]

Hi @Anonymous ,

You also need to add the year to the filtering:

Measure =
VAR temp_table =
    FILTER (
        SUMMARIZE ( ALL ( 'Table' ); 'Table'[DAte]; 'Table'[Headcount] );
        'Table'[Month] = SELECTEDVALUE ( 'Table'[Month] ) &&
        'Table'[Year] = SELECTEDVALUE ( 'Table'[Year] )
    )
RETURN
    CALCULATE ( SUMX ( temp_table; 'Table'[Headcount] ) )

[amitchandak]

@Anonymous , Not very clear with data

you can try

sumx(SUMMARIZE( Table, Table[Headcount],Table[Year],Table[Month]),[Headcount])