cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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

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

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

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
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors