Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
                This is tagged powerquery but the query language isn't M. Do you want to expand your question or change the tags?
– Carl Walsh
                Feb 8, 2016 at 20:29
                I'm a bit confused by your example - all your references are to a single "Sales" table? Where is the Quantity column?
– Mike Honey
                Feb 8, 2016 at 22:34
                @CarlWalsh I think I was confused as I had come from the Query Editor in PowerBI (which is also used in PowerQuery so samesame) but was using the AddColumn in Reports which then jumps out of M over to DAX. Correct me if I am wrong on that.
– Jay Killeen
                Feb 8, 2016 at 23:38
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
                Awesome. I was actually about to comment about using FILTER as you edited your answer. Which I picked up from the PowerPivotPro link. TotalQuantity = CALCULATE(SUM([Quantity]),FILTER(Sales,Sales[CustomerId]=EARLIER(Sales[CustomerI‌​d]))) worked! Thank you!
– Jay Killeen
                Feb 8, 2016 at 23:33
                And yes sorry, I should have had the CustomerId column named as Quantity but was rushing. I won't change anything as it will mean the answers need to be edited. The solution still works. Thanks again.
– Jay Killeen
                Feb 8, 2016 at 23:36
                ALLEXCEPT() is cleaner than FILTER() here:    CALCULATE( <expression>, ALLEXCEPT( 'Sales', 'Sales'[CustomerId] ) ) and performs better in a large table.
– greggyb
                Feb 9, 2016 at 14:38
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.