Forum Discussion
Helper IIICalculated column for cumulative total by another column
Rather than create this as a calculated column, why not give it a crack as a calculated measure instead.
When you add the measure to a grid or matrix visual you should see the result you expect.
Calculated columns can only work with data along the same row, and can't work with data on other rows. Trust me, you'll still be able to create your reports as expected. :)
Microsoft EmployeeHi ContabilidadBI,
Though I don't understand your requirements totally, I would give you my suggestions.(In case you may figure it out yourself with some of my suggestions.:smileyhappy:)
First, you should be able to use the formula below to create a calculate column for cumulative by making use of VAR function(DAX).
Acumulado =
VAR currentFecha = Albaranes[Fecha]
VAR currentCliente = Albaranes[Cliente]
RETURN
CALCULATE (
SUM ( Albaranes[Base] );
FILTER (
ALL ( Albaranes );
Albaranes[Fecha] <= currentFecha
&& Albaranes[Cliente] = currentCliente
)
)Then you should be able to make use of RANK.EQ function(DAX) to create the "Order" column in your table. The formula should be similar like below.
Order =
VAR currentMes = Albaranes[Mes]
VAR currentCliente = Albaranes[Cliente]
VAR currentAcumulado = Albaranes[Acumulado]
RETURN
CALCULATE (
RANK.EQ ( currentAcumulado; Albaranes[Acumulado] );
FILTER (
ALL ( Albaranes );
Albaranes[Mes] = currentMes
&& Albaranes[Cliente] = currentCliente
)
)
Regards
Helper IIIThanks v-ljerr-msft,
Not exactly what I was looking for but your comment was really helpul. Learned a lot with it.
Good luck!