Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'd like to create a column that calculates the running total of a measure. It's a bit more complicated than that, because it uses two measures: Over and Under. If the previous Over > 0, then add it to the current Over. If the previous Under > 0, then subtract it from the current Over. If that difference is positive, then show that. If it is negative, make the current equal to zero. I don't want the Under amounts to be summed, only the Over.
Here's some sample data:
Under | Over |
0 | 4 |
0 | 10 |
11 | 0 |
14 | 0 |
0 | 4 |
0 | 4 |
0 | 4 |
I want the running total column to be:
RunningTotal
4 = 4 from Over
14 = 4 + 10 from Over
3 = 14 - 11 from Under
0 = from Over
4 = from Over
8 = 4 + 4 from Over
12 = 8 + 4 from Over
Table:
Under | Over | Running Over Total - Under |
0 | 4 | 4 |
0 | 10 | 14 |
11 | 0 | 3 |
14 | 0 | 0 |
0 | 4 | 4 |
0 | 4 | 8 |
0 | 4 | 12 |
Here's what I have so far, but it's not working:
RunningTotal =
var filterDate = LASTDATE(Sheet1[Date])
var output = CALCULATE(
IF (
[Under] > 0,
[Over] - [Under],
[Over]
),
ALL(Sheet1),
Sheet1[Date] <= filterDate
)
RETURN IF (
output > 0,
ouput,
0
)
It's returning:
RunningTotal |
4 |
14 |
3 |
0 |
0 |
0 |
1 |
Please help me understand why my measure isn't working, and how to achieve my goal.
Hi , @Lyssillic
I'm sorry that I tried for a while but still didn't work out.
The logic is easy to understand but the formula is not easy to write.
Expect others to help you and share it with more people.
In addition, I will try to find someone who is familiar with this to take a further look at this.
I will post back if we have any conclusions on this topic.
Best Regards,
Community Support Team _ Eason
Hi @Lyssillic
agree with @v-easonf-msft : the logic is understandable, but unfortunately my DAX skills are not good enough to come up with a solution here. Problem is that you need "real" recurion to set back to 0 with regards to the previous value. Problem with the current formula is that the negative running total caused by the 14 "under" will have to be compensated by the following overs before getting positive again.
With regards on how to convert your existing measure to a calculated column - it would be this:
RunningTotal =
var filterDate = LASTDATE(Sheet1[Date])
var output = CALCULATE(
SUMX(Sheet1, IF (
[Under] > 0,
[Over] - [Under],
[Over]
)),
ALL(Sheet1),
Sheet1[Date] <= filterDate
)
RETURN IF (
output > 0,
output,
0
)
But it will just return the same value than your current measure in the column.
In Power Query one would use List.Generate or List.Accumulate, but if you run them without a Table.Buffer (as this wouldn't work in Direct Query mode) the result will be very slow for larger datasets.
Posting the PQ sample here in case it helps some DAX genius to pick up the logic:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTKBYgNDXSAyMjC0VIrVgUgZggmwpBGKpKEhSAyIjUG0MaqcCVQOjE0wDIXbZ4pVygIkZYZVytAIJGeOkIsFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Under = _t, Over = _t, #"Running Over Total - Under" = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Under", Int64.Type}, {"Over", Int64.Type}, {"Running Over Total - Under", Int64.Type}, {"Date", type date}}),
BufferedTable = Table.Buffer(#"Changed Type"),
RunningTotal = List.Skip(List.Generate( () =>
[RowResult = 0, CumulativeResult = 0, Counter = 0],
each [Counter] <= Table.RowCount(BufferedTable),
each [
CurrentRow = BufferedTable{[Counter]},
RowResult = if CurrentRow[Under] > 0
then CurrentRow[Over] - CurrentRow[Under]
else CurrentRow[Over],
CumulativeResult = List.Max( { [CumulativeResult] + RowResult, 0 } ),
Counter = [Counter] + 1
],
each [CumulativeResult]
)),
Result = Table.FromColumns( Table.ToColumns( BufferedTable ) & { RunningTotal }, Table.ColumnNames(BufferedTable) & {"RunningTotal"} )
in
Result
Actually, even without the buffer the complicated List.Generate-logic wouldn't work in Direct Query unfortunately.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi
there is usually no use for measures in calculated columns.
That's because calculated columns are calculated/refreshed when you refresh the data model to catch changes in the underlying data.
Measures on the other hand respond to user interactions with the reports.
So if you want to create a column with DAX it's best practice to do this with column operations only.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF Okay, could you help me translate this into a calculated column? Also, I'm restricted to only use DirectQuery.
User | Count |
---|---|
128 | |
108 | |
99 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |