How to create an accumulate measure?

[RuthMerchán]

Hi everyone!

Please, could you help me with some idea to how create an accumulated measure in the following scenario?:

in the report I have created two tables:

-Table 1 with information about fields tou can see for an specific year, month and 1 move_type "sal_prod"

Table2: with information about all without limit of time and all mov_types.

So, if I select one row in the first table, as the example in yellow, automatically, you can see on the table 2 the detail for that reference, batch,format, with all amounts for all the dates and mov_types available related to it. 

The goal is to get a measure (blue column "Accum. amount") that does a running sum of the column "amount" ordered in the way you can see, with date asc. 

By now, I have implemented this formula:

Accum. amount =

CALCULATE(SUM(FACT_MOVIMIENTOS_TURNOS[amount]),

FILTER(ALLSELECTED(FACT_MOVIMIENTOS_TURNOS),

FACT_MOVIMIENTOS_TURNOS[date] <= max(FACT_MOVIMIENTOS_TURNOS[date])

))

but something is wrong because it looks like it is working because it shows the same total column (666), but the accumulated is not correct in each row on table 2. I would like to get the same result as you can see on the screenshot.

Furthermore, When I deselect or remove the selection for that row on the table 1, the table 2 late a lot of time (very much) processing the view. There is a problem of performance...

Please, could you help me?

Thank you so much for your support.

Please, if you need more information about it, dont hesitate to ask me.

Ruth

[mlsx4]

Okey, I have review the data and you are getting the correct value. Amount shows the movement that has been made at this date. But the cummulative is applying the movement of Amount to the total. So, I don't understand what happens or what are you trying to achieve.

The performance of a table is affected by the number of columns you include in the visual... Maybe you should consider to split it or reduce the columns you show

[RuthMerchán]

Hi! @mlsx4 

I have tried to modified the formula using dim_tiempo for dates, like this:

Litros Acumulados =

TOTALYTD(SUM(FACT_MOVIMIENTOS_TURNOS[LITROS]),DIM_TIEMPO[FECHA_ID])

and it looks like it's working, but I don't know what order it is following because the accumulated sum it is not correct in each row...

For exampli, in this case (example) you can see how after I select the row in table 1, the table 2 give details about that product and it looks like it is working the accumulated sum in the two first rows, but the rest are wrong...

Please, could you explain why this happens?

Thank you very much!

[mlsx4]

Hi @RuthMerchán 

I think it is accumulating by tipo_mov. Could be?

[RuthMerchán]

Exactly @mlsx4 ! As you say, it is accumulating by tipo_mov. But not only by tipo_mov, but also by usuario.

Here, you can see as I am saying (the product is always the same in table 2 when I select one row in table 1):

Is there any way so that it can show the accumulated without taking into account those columns?

Thanks you!!

[mlsx4]

Hi again, @RuthMerchán 

You can add an all filter in order to avoid filters affecting the result:

Litros Acumulados = TOTALYTD(CALCULATE(SUM('Table'[litros]), ALL('Table'),FILTER('Table','Table'[referencia]<>BLANK())),'Calendar'[Dates])

P.S. Maybe you won't need the filter, just the all. In my case, I had empty dummy data in the example and need to be removed.

[mlsx4]

Hi @RuthMerchán 

Have you tried with: TOTALYTD(SUM(Table2[Amount]),Calendar[Date]) ?*

*I'm using a time intelligence table called Calendar

[RuthMerchán]

Hi @mlsx4 

I have just tried it, but the result is the same as column amount. And the problem of performance (when I remove the selection of a row on table 1) continues... 😞

Thank you for your support