Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
@Russ - Maybe try:
Runtotal Amount By TransID =
CALCULATE (
[Total Amount],
FILTER ( ALL ( Ledger[TransID] ), Ledger[TransID] <= MAX ( Ledger[TransID] ) )
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |