Forum Discussion
Cumulative Total ... help!!
Hi all,
please note that I am trying to do the following (after 6 hours of failures)
Cumulative Sum of Total Commission / Tot Employee..
Acumulado Empleado Month 1 ... 861K/ 2035
Acumulado Empleado Month 2 ... (861K+849K)/ avg(2035+2035)
Acumulado Empleado Month 3 ... (1.4M + 861k+ 849k)/ avg. (2035+2035+2035) .....
the formula used is
Anonymous , Almost the same time update. This because Avg is at the day level not at month level
Try like
Acumulado empleado = calculate(divide( CALCULATE(SUM('2019'[COMISION TOTAL]), filter(ALL('2019'), '2019'[Mes fecha]<=MAX('2019'[Mes fecha]))), averagex(summarize(Table,Table[Mes Nomber],"_sum",CALCULATE(sum('2019'[TOTEMP]),filter(ALL('2019'), '2019'[Mes fecha]<=MAX('2019'[Mes fecha])))),[_sum]) ) )You can use allselected too.
5 Replies
I am not clear on what "doesn't work" but if you are referring to the total row of the table visualization, This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Anonymous
Put this calculation individually and check which one is not working.To me, this is working as Avg employee will remain almost same and the numerator is increasing and your calculation of "Acumulado empleado " is increasing.
You can also try
Acumulado empleado = calculate(divide(
CALCULATE(SUM('2019'[COMISION TOTAL]), filter(ALL('2019'), '2019'[Mes fecha]<=MAX('2019'[Mes fecha]))),
CALCULATE(average('2019'[TOTEMP]),filter(ALL('2019'), '2019'[Mes fecha]<=MAX('2019'[Mes fecha])))))Anonymous , Almost the same time update. This because Avg is at the day level not at month level
Try like
Acumulado empleado = calculate(divide( CALCULATE(SUM('2019'[COMISION TOTAL]), filter(ALL('2019'), '2019'[Mes fecha]<=MAX('2019'[Mes fecha]))), averagex(summarize(Table,Table[Mes Nomber],"_sum",CALCULATE(sum('2019'[TOTEMP]),filter(ALL('2019'), '2019'[Mes fecha]<=MAX('2019'[Mes fecha])))),[_sum]) ) )You can use allselected too.
