Forum Discussion
Reference current row in a measure
Dear community,
I am more and more getting desparate with the following issue and therefore now turning to you. Until recently I was an Excel power user, but now turned towards PowerBi, which I am mostly very happy about.
The issue at hand is that I want to create a measure that uses the current row value as a reference. More specifically, I want to create a sum across a column for all rows which have a reference value (other column) >= the current row. I found out that this can be done in a "New Column" relatively easily. However, I need a measure as it should be processed further and must be adaptive to filtering in the report.
I attached an examplary table and a screenshot of how I would create that funtionality in Excel (in case it helps).
| Reference | Values to sum up | Cumulative Sum |
| 1 | 3.230516107 | 12.24387441 |
| 1 | 0.880767646 | 12.24387441 |
| 1 | 0.940916486 | 12.24387441 |
| 1 | 0.309598663 | 12.24387441 |
| 1 | 0.137565722 | 12.24387441 |
| 2 | 0.920849434 | 6.744509782 |
| 3 | 0.292905447 | 5.823660349 |
| 3 | 0.293494306 | 5.823660349 |
| 3 | 0.619171052 | 5.823660349 |
| 3 | 0.447142839 | 5.823660349 |
| 3 | 0.035413509 | 5.823660349 |
| 3 | 0.02673483 | 5.823660349 |
| 4 | 0.514089092 | 4.108798367 |
| 4 | 0.297866241 | 4.108798367 |
| 4 | 0.656081161 | 4.108798367 |
| 4 | 0.163896406 | 4.108798367 |
| 5 | 0.564305993 | 2.476865465 |
| 5 | 0.796432183 | 2.476865465 |
| 6 | 0.253567742 | 1.11612729 |
| 6 | 0.862559548 | 1.11612729 |
Thanks so much in advance!
Steffen
Anonymous , try a new measure like
calculate(sum(Table[Values to sum]), filter(allselected(Table), Table[Values to sum] >=max(table[Values to sum])))
or new column like
sumx(filter(Table,Table[Values to sum] >=earlier(table[Values to sum])),[Values to sum])
2 Replies
- amitchandak
Super User
Anonymous , try a new measure like
calculate(sum(Table[Values to sum]), filter(allselected(Table), Table[Values to sum] >=max(table[Values to sum])))
or new column like
sumx(filter(Table,Table[Values to sum] >=earlier(table[Values to sum])),[Values to sum])
amitchandak Thanks a lot!
The "measure"-version is what I was looking for. I replaced the columns within the filter bracket to the "Reference" column and that produces the desired result.It still feels odd having to use the max() operator just to reference the current row, though.