# Developers Planet

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

Sales

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

``````=SUMIFS(Sales[Quantity],Sales[CustomerId],[@[CustomerId]])
``````

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:

http://www.powerpivotpro.com/2013/07/writing-a-subtotal-calc-column-aka-the-simplest-use-of-the-earlier-function/

And FWIW here is the official doco on EARLIER:

https://msdn.microsoft.com/en-us/library/ee634551.aspx