Problems with SUMX

[Anonymous]

Hi, i am trying to accumulate the results of the following measure:

ind_rotacion =

   VAR rota_colab =

      SUM(Sheet1[ROTACION])

   VAR totalcolaborador=

      COUNT(Sheet1[Contrato])

RETURN

   DIVIDE(rota_colab,totalcolaborador)

I am using a SUMX function as follows:

rotacion_acum = SUMX(FILTER(ALLSELECTED(Sheet1),Sheet1[Mes]<=MAX(Sheet1[Mes])),[ind_rotacion])

i have a database with the information of the active employees during each month and a colum with a 1 or a 0 if they quit or not (Sheet1[ROTACION])

The results i get from the sumx function, equals the accumulated sum of the monthly rotation Sheet1[ROTACION] even though the expression i used is [ind_rotacion] 

rotation rate = [ind_rotacion] 

the result i get = rotacion_acum = SUMX(FILTER(ALLSELECTED(Sheet1),Sheet1[Mes]<=MAX(Sheet1[Mes])),[ind_rotacion])

Thank you so much for your help in advance! Can I accumulate a measure?  

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Refer the formula.

Measure = SUMX(FILTER(ALLSELECTED(sheet1[Mes]),sheet1[Mes]<=MAX(sheet1[Mes])),[ind_rotacion])

Best Regards,

Jay

[Anonymous]

Thank you so much Jay, i've been looking for this answer quite some time now. it worked!! Would you please explain me the difference between filter by Sheet1 and filter by Sheet1[month]. It made a difference in the result 

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? with based columns

try like

divide( SUMX(FILTER(ALLSELECTED(Sheet1),Sheet1[Mes]<=MAX(Sheet1[Mes])),Sheet1[ROTACION]) ,

COUNTX(FILTER(ALLSELECTED(Sheet1),Sheet1[Mes]<=MAX(Sheet1[Mes])),Sheet1[Contrato]))

[Anonymous]

Thank you so much for the quick response. I tryed the DAX fuction and it gave me zero. I think in there we would be acumulating the number of employees that quit divided by the monthly count of the employees. But i need to acumulate the result of that division. Sadly i cannot share an exact data sample, but i made one. 

Anio	Mes	cod_mes	Contrato	Nombre	Identificacion	ROTACION	fecha_mensual
2021	1	20211	566687901	peter parker	111111111	0	1/01/2021
2021	2	20212	566687901	peter parker	111111111	0	1/02/2021
2021	3	20213	566687901	peter parker	111111111	0	1/03/2021
2021	4	20214	566687901	peter parker	111111111	0	1/04/2021
2021	5	20215	566687901	peter parker	111111111	0	1/05/2021
2021	6	20216	566687901	peter parker	111111111	1	1/06/2021
2021	1	20211	77889011	samantha jhonson	222222222	0	1/01/2021
2021	2	20212	77889011	samantha jhonson	222222222	0	1/02/2021
2021	3	20213	77889011	samantha jhonson	222222222	0	1/03/2021
2021	4	20214	77889011	samantha jhonson	222222222	0	1/04/2021
2021	5	20215	77889011	samantha jhonson	222222222	0	1/05/2021
2021	6	20216	77889011	samantha jhonson	222222222	0	1/06/2021
2021	1	20211	33300011	peter parker	333333333	0	1/01/2021
2021	2	20212	33300011	peter parker	333333333	1	1/02/2021
2021	1	20211	88888888	john doe	44444444	0	1/01/2021
2021	2	20212	88888888	john doe	44444444	0	1/02/2021
2021	3	20213	88888888	john doe	44444444	0	1/03/2021
2021	4	20214	88888888	john doe	44444444	0	1/04/2021
2021	5	20215	88888888	john doe	44444444	0	1/05/2021
2021	6	20216	88888888	john doe	44444444	0	1/06/2021
2021	1	20211	76514238	john doe	55555555	0	1/01/2021
2021	2	20212	76514238	john doe	55555555	0	1/02/2021
2021	3	20213	76514238	john doe	55555555	0	1/03/2021
2021	4	20214	76514238	john doe	55555555	0	1/04/2021
2021	5	20215	76514238	john doe	55555555	1	1/05/2021

this is kinda what i have the total of employees varies each month and i have a dummy column with a 1 in the month they rotated so i sum those ones and count the total of contracts and then i divide it getting the ind_rotation measure. In a table an add the month the sum of the rotation, the count of the contracts and the ind_rotation measure and it gaves me the monthly indicator which i need to accumulate 

I need another measure to acummulates the ind_rotation in order to add it to the table. also i need it to be this way cuz i need to filter by zone and management office

This would be the output table

count of contracts	sum of rotation	ind_rotation	accum ind_rotation
1277	15	1,17%	1,17%
1322	18	1,36%	2,54%
1332	24	1,80%	4,34%
1331	31	2,33%	6,67%
1363	21	1,54%	8,21%
1371	12	0,88%	9,08%

 

[Anonymous]

Thank you so much for the quick response. I tryed the DAX fuction and it gave me zero. I think in there we would be acumulating the number of employees that quit divided by the monthly count of the employees. But i need to acumulate the result of that division. Sadly i cannot share an exact data sample, but i made one. 

Anio	Mes	cod_mes	Contrato	Nombre	Identificacion	ROTACION	fecha_mensual
2021	1	20211	566687901	peter parker	111111111	0	1/01/2021
2021	2	20212	566687901	peter parker	111111111	0	1/02/2021
2021	3	20213	566687901	peter parker	111111111	0	1/03/2021
2021	4	20214	566687901	peter parker	111111111	0	1/04/2021
2021	5	20215	566687901	peter parker	111111111	0	1/05/2021
2021	6	20216	566687901	peter parker	111111111	1	1/06/2021
2021	1	20211	77889011	samantha jhonson	222222222	0	1/01/2021
2021	2	20212	77889011	samantha jhonson	222222222	0	1/02/2021
2021	3	20213	77889011	samantha jhonson	222222222	0	1/03/2021
2021	4	20214	77889011	samantha jhonson	222222222	0	1/04/2021
2021	5	20215	77889011	samantha jhonson	222222222	0	1/05/2021
2021	6	20216	77889011	samantha jhonson	222222222	0	1/06/2021
2021	1	20211	33300011	peter parker	333333333	0	1/01/2021
2021	2	20212	33300011	peter parker	333333333	1	1/02/2021
2021	1	20211	88888888	john doe	44444444	0	1/01/2021
2021	2	20212	88888888	john doe	44444444	0	1/02/2021
2021	3	20213	88888888	john doe	44444444	0	1/03/2021
2021	4	20214	88888888	john doe	44444444	0	1/04/2021
2021	5	20215	88888888	john doe	44444444	0	1/05/2021
2021	6	20216	88888888	john doe	44444444	0	1/06/2021
2021	1	20211	76514238	john doe	55555555	0	1/01/2021
2021	2	20212	76514238	john doe	55555555	0	1/02/2021
2021	3	20213	76514238	john doe	55555555	0	1/03/2021
2021	4	20214	76514238	john doe	55555555	0	1/04/2021
2021	5	20215	76514238	john doe	55555555	1	1/05/2021

this is kinda what i have the total of employees varies each month and i have a dummy column with a 1 in the month they rotated so i sum those ones and count the total of contracts and then i divide it getting the ind_rotation measure. In the report i created a table and added the month, the sum of the rotation, the count of the contracts and the ind_rotation measure and it gaves me the monthly indicator which i need to accumulate 

I need another measure to acummulates the ind_rotation in order to add it to the table. also i need it to be this way cuz i need to filter by zone and management office 

count of contracts	sum of rotation	ind_rotation	acum ind_rotation
1277	15	1,17%	1,17%
1322	18	1,36%	2,54%
1332	24	1,80%	4,34%
1331	31	2,33%	6,67%
1363	21	1,54%	8,21%
1371	12	0,88%	9,08%

Again, thank you so much for your help