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

**Sales**

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

I could add a formula:

```
=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?

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:

Asked in February 2016

Viewed 3,145 times

Voted 8

Answered 1 times

Viewed 3,145 times

Voted 8

Answered 1 times