Forum Discussion
Solved
Running Totals AND SUM in the same Matrix
I'm currently developing a cash flow matrix and failed while trying to merge two measures in one (running totals and sum):
My idea was to add boolean indicators (Calculado Column) as such:
So that my measure could do the following:
Subtotal =
var Calculado = SELECTEDVALUE('Categorias'[Calculado])
var Runningtotal= [Running total]
Return
SWITCH(
True(),
Calculado = 0, [Total],
Calculado = 1, Runningtotal,
BLANK()
)
The referenced measures are:
Running total =
CALCULATE(
[Total],
FILTER(
ALLSELECTED('Calendário'[Data]),
ISONORAFTER('Calendário'[Data], MAX('Calendário'[Data]), DESC)
)
)
The [Total] measure:
[Total] = SUM(Movimentacoes[nValorTitulo])When [Running Total] is in a matrix by itself it works absolutely fine, also does [Total], however if I mix both using the [Subtotal] measure, the [Running Total] results in 0 and the sum measure [Total] works just fine.
Is there any way to mix both of those in the same matrix? or maybe a way to scroll through two matrix visuals at the same time?
Managed to fix it:
Had to add an Index column and edit the measure:Subtotal =
var Calculado= SELECTEDVALUE(Categorias[Calculado])
var ordemContexto = Max('Categorias'[Índice])
var ValorSubtotalresumo =
CALCULATE([Running Total],Filter(ALLNOBLANKROW('Categorias'), 'Categorias'[Índice] >= ordemContexto))
Return
SWITCH(
True(),
Calculado = 0, [Total],
Calculado = 1, ValorSubtotalresumo,
BLANK()
)
1 Reply
Managed to fix it:
Had to add an Index column and edit the measure:Subtotal =
var Calculado= SELECTEDVALUE(Categorias[Calculado])
var ordemContexto = Max('Categorias'[Índice])
var ValorSubtotalresumo =
CALCULATE([Running Total],Filter(ALLNOBLANKROW('Categorias'), 'Categorias'[Índice] >= ordemContexto))
Return
SWITCH(
True(),
Calculado = 0, [Total],
Calculado = 1, ValorSubtotalresumo,
BLANK()
)