Forum Discussion

ContabilidadBI's avatar
ContabilidadBI
Icon for Helper III rankHelper III
9 years ago
Solved

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 t...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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's avatar
ContabilidadBI
Icon for Helper III rankHelper III
9 years ago

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's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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

  • ContabilidadBI's avatar
    ContabilidadBI
    Icon for Helper III rankHelper III
    9 years ago

    Thanks v-ljerr-msft,

     

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

     

    Good luck!