Home Ask Login Register

Developers Planet

Your answer is one click away!

Jay Killeen February 2016

PowerBi DAX equivalent for SUMIFS with current row value as filter

In Excel I could, if I was in a table called 'Sales' that had four columns


Month, CustomerId, ProductId, TotalQuantity
Jan,1, CAR,
Feb,1, CAR,

I could add a formula:


That would go to the Sales table and sum the CustomerID column filtered by the CustomerID of the current row where the formula has been entered.

I am attempted to replicate this in a PowerBI Calculated Row but I can't get the @ working for a row reference. It comes across like

TotalQuantity = CALCULATE(SUM(Sales[Quantity]),Sales[CustomerId] = Sales[CustomerId]))

Any idea how to get the equivalent @ working?


Mike Honey February 2016

I think the key function you are missing is EARLIER. That is not surprising because it has a misleading name - it really means "Current Row". You also need a FILTER function in the Filter parameter of CALCULATE, to reset the filter context to the entire table.

So your New Column function might look like this:

TotalQuantity = CALCULATE(SUM(Sales[Quantity]), FILTER(Sales, Sales[CustomerId] = EARLIER (Sales[CustomerId])))

Here's a neat example, from the most accessible source site for DAX formulas:


And FWIW here is the official doco on EARLIER:


Post Status

Asked in February 2016
Viewed 3,145 times
Voted 8
Answered 1 times


Leave an answer

Quote of the day: live life