Problematic running total measure by transaction ID - slow and inconsistent.

[Russ]

Have a Power Bi model with two tables - Ledger and Dates - linked by date field.

Ledger contains the following fields, with data for several years.

I would like to produce a matrix with the following fields:

Ledger [TransID]

Dates [Date]

[Total Amount] - Measure

[Runtotal Amount By TransID] - Measure

I have the following two measures:

Total Amount = 
    SUM ( Ledger[Amount] )


Runtotal Amount By TransID =
CALCULATE (
    [Total Amount],
    ALL ( Dates[Date] ),
    FILTER ( ALL ( Ledger[TransID] ), Ledger[TransID] <= MAX ( Ledger[TransID] ) )

I would like the Runtotal to be by TransID and not date. Also, to be calculated over all time, hence All (Dates[Date]. The matrix will also be sliced by date and/or account, but this should have no affect on the RunTotal measure. 

The Runtotal measure seems to work ok on its own and also in a matrix alongside  Ledger [TransID] and [Total Amount]. This takes just over a second to compute.

However, as soon as I add the Dates [Date] field to the matrix the compute time runs into minutes. 

I'm hoping there is a better way of achieving my desired output. I have messed around with Summarize and AddCoulmns, but to no avail.

Any ideas?

[v-lili6-msft]

HI  @Russ 

You may try this formula as below:

Runtotal Amount By TransID = 
CALCULATE (
    [Total Amount],
    ALL ( Dates ),
    FILTER ( ALL ( Ledger[TransID] ), Ledger[TransID] <= MAX ( Ledger[TransID] ) ))

Use ALL ( Dates ) instead of ALL ( Dates[Date] )

Regards,

Lin

[Greg_Deckler]

@Russ - Maybe try:

Runtotal Amount By TransID =
CALCULATE (
    [Total Amount],
    FILTER ( ALL ( Ledger[TransID] ), Ledger[TransID] <= MAX ( Ledger[TransID] ) )

[Russ]

@Greg_Deckler  - thanks, but that does not fix it. Performance is exactly the same. Also, if you slice by date it makes the Runtotal incorrect.