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?

Answers


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

Post Status

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

Search




Leave an answer