Forum Discussion
Problems getting cumulative totals
I'm having trouble getting the cumulative total. I can see the monthly total, but it does not accumulate as I would like. I couldn't find the logic of the thing.
I have the following:
Valor por Contencioso =
SUMX(
'table - Fichas (Entrada e Saída)',
[Nº Fichas Contencioso] * 'table - Fichas (Entrada e Saída)'[Valor unitário]
)
In the monthly, using dCalendar, it returns the right values, but the Total line brings me the average and not the sum of the total for each month.
The measure "Nº Fichas Contencioso" used above is the following.
Nº Fichas Contencioso =
CALCULATE(
COUNTROWS('table - Fichas (Entrada e Saída)'),
FILTER(
'table - Fichas (Entrada e Saída)',
'table - Fichas (Entrada e Saída)'[Data_Entrada] <= LASTDATE(dCalendario[Data])
&&
('table - Fichas (Entrada e Saída)'[Data_Fim] >= FIRSTDATE(dCalendario[Data])
|| 'table - Fichas (Entrada e Saída)'[Data_Fim] = BLANK())
),
'table - Fichas (Entrada e Saída)'[Materia_Tipo] = 1 || 'table - Fichas (Entrada e Saída)'[Materia_Tipo] = 2,
'table - Contratos'[Ativo] = 1,
'table - Contratos'[Classe_Contrato] = "O",
'table - Fichas (Entrada e Saída)'[Fase_Cod] <> 67 && 'table - Fichas (Entrada e Saída)'[Fase_Cod] <> 101,
'table - Fichas (Entrada e Saída)'[N_Contrato] <> BLANK()
)
My result in an array:
Could someone shed some light on what I'm doing wrong?
Thanks for any help.
2 Replies
- amitchandak
Super User
Bruno_OMB , for cumulative better to use a measure like
Cumm Sales = CALCULATE([Your Measure],filter(all('Date'),'Date'[date] <=max('Date'[date])))
Cumm Sales = CALCULATE([Your Measure],filter(allselected(date),date[date] <=max(date[Date])))
Or use window function
Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41
Thanks for the help!
But I can't get the result I need. Even using the suggested methods, the result is the same.
I would like to add up the monthly totals in the grand total row of the matrix. (10,800 + 11,300 + 11450....)
