SUMIFS Reference but in BI

Question

I would like to transfer a Excel sheet to BI to see if the execution time improves, but I'm have difficulties in replicating one of the formulas. I have a list of data and use the following formula:

=SUMIFS($B$2:B2,$A$2:A2,A2)

When I drag the formula, B2 and A2 stay the same:

=SUMIFS($B$2:B1,$A$2:A12,A12)

And here's an example of the sheet itself:

The formula in Excel does exactly what I need, but I couldn't replicate it in BI. Is it possible to do it?

Edit1: Adding a second image (answering a comment):

user_10671926 · Answer

You almost solved it yourself Daniel. The only issue you have is capturing the current row's value. Here's one way to do that:

Coluna = 
    var ThisStep = Plan1[Step]
    var ThisLocation = Plan1[Location]
return
    CALCULATE(
        SUM(Plan1[Type of Change]),
        FILTER(ALL(Plan1), Plan1[Step] <= ThisStep && Plan1[Location] = ThisLocation)
    )

The result:

SUMIFS Reference but in BI

1 Answers