Running total for a table (measure)

[Anonymous]

hi forum,

quick one for you: I have this visual, wich is the result of fitlers and other calculate columns. What i miss is the "cumulative" column. all the examples i found to make this field are related to a date or an index which in this case are missing. 

Is there any way to make this column without referring to another field? i've tried to add the index however, due to the nature of this table (and visual) it comes out screwed.

Orig	Dest	Dept Date From	Dept Day To	DAY COUNT	cumulative
ADL	BNE	25/11/2018	24/05/2019	180	180
ADL	MEL	25/11/2018	24/05/2019	180	360
ADL	SYD	10/11/2018	9/05/2019	180	540
BNE	DRW	25/11/2018	24/05/2019	180	720
CBR	BNE	10/11/2018	9/05/2019	180	900
CBR	MEL	25/11/2018	24/05/2019	180	1080
CBR	SYD	10/11/2018	9/05/2019	180	1260
MEL	DRW	25/11/2018	24/05/2019	180	1440
MEL	PER	25/11/2018	24/05/2019	180	1620
PER	BNE	10/11/2018	9/05/2019	180	1800
PER	CBR	25/11/2018	24/05/2019	180	1980
PER	MEL	25/11/2018	24/05/2019	180	2160
PER	SYD	25/11/2018	24/05/2019	180	2340
SYD	BNE	25/11/2018	24/05/2019	180	2520
SYD	DRW	25/11/2018	24/05/2019	180	2700
SYD	MEL	10/11/2018	9/05/2019	180	2880

any ideas in mind?

[v-jiascu-msft]

Hi @Anonymous,

Please check out the demo in the attachment. Is it what you need?

Measure =
CALCULATE (
    SUM ( Table1[DAY COUNT] ),
    FILTER ( ALL ( Table1 ), Table1[Index] <= MIN ( Table1[Index] ) )
)

Best Regards,

Dale

[Anonymous]

Hi Dale, 

i tried to index that table and it doesn't work as it should. The table that i've uploaded here is the reconstruction of the visual that i have, which is the result of several filters together. I have to action this running total on that filtered visual, that's why the index option didn't work i believe...

I can give a try with your model and see if it work

[v-jiascu-msft]

Hi @Anonymous,

Please share your pbix file if it's convenient.

Best Regards,

Dale

[Anonymous]

Unfortunately I can't as it has sensitive data. 

I can try to work out the query in order to adhere to what it should be in the visual.

Or what if i make a calculated column: but in this case i can add an index only to those rows involved with the running total.

Would it be a solution?

[v-jiascu-msft]

Hi @Anonymous,

Maybe you can point out what it should be in my demo. Then we can find a solution or a workaround.

Best Regards,

Dale

[Anonymous]

 Hi @v-jiascu-msft

I was checking your model. I wanted to ask you:

for columnts DAY COUNT and Cumulative: is this data gathered from a source or are those calculated columns?

But this model reflect a solution that may work out if i change some features of my model. 

The fundamental difference is that the list of rows that you have is already refined. I have thousands of rows that i don't need and i need to come up with like 17, and on these ones i have to perform those actions. 

[Anonymous · Accepted solution]

Hi @v-jiascu-msft

i'm getting there 😄

So i included a refining critera that narrows down a lot the whole query. What i have to do now to get to the point of your model (literally the same) is coming up with an idea for this:

Orig	Dest	Dept from	Dept to	AIF
XXXX	YYY	10/11/18	20/12/18	100
XXXX	YYY	05/11/18	20/12/18	120

So the situation is that, in my final table (the one that then becomes a visual) has only to have the lowest AIF depending on Orig and Dest (or Orig&Dest) because I have to come up with the cheapest AIF.

If we sort this out, then your model fits. 

😄