Forum Discussion
Distinct sum issue
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 |
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] ) )
4 Replies
Anonymous , Not very clear with data
you can try
sumx(SUMMARIZE( Table, Table[Headcount],Table[Year],Table[Month]),[Headcount])
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.
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] ) )
