Calculated column for cumulative total by another column

[ContabilidadBI]

Good afternoon everyone,

I have been trying  for a few hours to get a new column that gets me the running sum of a column by client (another column in the same table). I have read other similar topics but somehow I am still trying to figure out how to do it correctly.

Here is a pic of the formula I am using, and as you can see, the results I am getting are the total sum for the whole table in each row of the new column.

I have always worked in financial accounting and just now getting more involved in analytical work so my experience is very low. But thanks to you guys I am learning a lot in this forum. I hope I will be soon in the position of helping other people too. Sorry if my question seems a little amateur like.

Ideally I would like to calculate the running total by client and also by month (so that new column would be 0 at the beginning of every month for every client), but for now I am still trying to calculate it without taking into account the month variable.

Any ideas please?

Sorry for my english (not my first language)

Thank you so much!!

[Phil_Seamark · Accepted solution]

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

[ContabilidadBI]

Thanks for your answer Phil.

I have been able to create what I wanted with a calculated measure (I don't know why I was trying to do it with a calculated column...).

Now I have this:

So I can see now that the  running sales for this client in the 5th order of January where 6.0 and in Febrerary 5th order 6.2. Both visualizations are filtered by client and month. Is this an orthodox way of doing this? seems a little rudimentary to me but I don't know much...

Also, is there a way to calculate the difference in %? So I would see that by the 5th order of febrerary the sales have increased 20% compared to the same order of the previous month? I don't know if thats even possible because there is no 'order' column with order 1, order 2.... in the rows.

THANKS!!!!!!!!!!!!!!

[v-ljerr-msft]

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

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

[ContabilidadBI]

Thanks v-ljerr-msft,

Not exactly what I was looking for but your comment was really helpul. Learned a lot with it.

Good luck!