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.
Hi
In my mind I scowerd the web to find examples, but failed.
I need sum the last 3 rows.
The left column is the index/rowno.
Middle column the values.
I need a way to calc the last column: which is the sum of this row's value + the values of the last 2 rows.
(I can't get Sum and TOPN to work.)
Row | Value | SumOfLast3Values |
1 | 5 | 5 |
2 | 10 | 15 |
3 | 15 | 30 |
4 | 20 | 45 |
5 | 25 | 60 |
6 | 30 | 75 |
7 | 35 | 90 |
8 | 40 | 105 |
9 | 45 | 120 |
10 | 50 | 135 |
Regards
Solved! Go to Solution.
You could do this either as a calculated column or as a measure, both have their advantaged and disadvantages. A measure would take account of any filters or slicers applied which a calculated column would not, but if it is a large dataset then a calculated column would likely give better performance.
To create a column you could use
Sum of last 3 values =
var currentRow = 'Table'[Row]
return CALCULATE( SUMX( TOPN(3, 'Table', 'Table'[Row]), 'Table'[Value]),
REMOVEFILTERS('Table'), 'Table'[Row] <= currentRow )
or as a measure
Sum of last 3 values =
var currentRow = SELECTEDVALUE('Table'[Row])
return CALCULATE( SUMX( TOPN(3, 'Table', 'Table'[Row]), 'Table'[Value]),
'Table'[Row] <= currentRow )
Hi @JapieTeunissen ,
I am sure there are multiple ways to do this, this was the first one which came to my mind. You need to create a measure which sums the "Value", second you need to create a rank of these SumValue and then you need to have a final running total which sums the rank of the last 3. See snippet, code and attached file
ValueTotal =
SUM ( SampleData[Value] )
ValueTotalRank =
RANKX ( ALL ( SampleData ), [ValueTotal],, ASC )
ValueTotalRunning =
VAR CurrentValue =
SELECTEDVALUE ( SampleData[Value] )
VAR CurrentRank = [ValueTotalRank]
VAR CurrentRankMinusThree = CurrentRank - 2
RETURN
CALCULATE (
[ValueTotal],
FILTER (
ALL ( SampleData ),
[ValueTotalRank] >= CurrentRankMinusThree
&& [ValueTotalRank] <= CurrentRank
)
)
Both solutions worked perfectly.
I have 30k rows, so I get "not enough memory..." for both, but that is not your problem.
You could do this either as a calculated column or as a measure, both have their advantaged and disadvantages. A measure would take account of any filters or slicers applied which a calculated column would not, but if it is a large dataset then a calculated column would likely give better performance.
To create a column you could use
Sum of last 3 values =
var currentRow = 'Table'[Row]
return CALCULATE( SUMX( TOPN(3, 'Table', 'Table'[Row]), 'Table'[Value]),
REMOVEFILTERS('Table'), 'Table'[Row] <= currentRow )
or as a measure
Sum of last 3 values =
var currentRow = SELECTEDVALUE('Table'[Row])
return CALCULATE( SUMX( TOPN(3, 'Table', 'Table'[Row]), 'Table'[Value]),
'Table'[Row] <= currentRow )
I have 30k rows, so I get "not enough memory..." for both, but that is not your problem.
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 |
---|---|
103 | |
101 | |
87 | |
73 | |
67 |
User | Count |
---|---|
119 | |
111 | |
95 | |
79 | |
72 |