Cumulative Total ... help!!

[Anonymous]

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 

Acumulado empleado = calculate(divide(

CALCULATE(SUM('2019'[COMISION TOTAL]), filter(ALLSELECTED('2019'), '2019'[Mes fecha]<=MAX('2019'[Mes fecha]))),

CALCULATE(average('2019'[TOTEMP]),filter(ALLSELECTED('2019'), '2019'[Mes fecha]<=MAX('2019'[Mes fecha])))))

but it doesn't work...

Anyone can help me... I will be very thankfull.

Simone

[amitchandak]

@Anonymous 
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])))))

[amitchandak · Accepted solution]

@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.

[Anonymous]

thank you sooo much!

[Greg_Deckler]

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/547907

[Anonymous]

thanks but the issue is the formula I am using