cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Calculated column for cumulative total by another column

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.

 

Captura_Ink_LI.jpg

 

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!!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

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

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Highlighted
Microsoft
Microsoft

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

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted

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:

 

Captura.PNG

 

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!!!!!!!!!!!!!!

Highlighted

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.Smiley Happy)

 

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

Highlighted

Thanks v-ljerr-msft,

 

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

 

Good luck!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors