I have a table in my PowerPivot data model called Orders which stores all our order records, each row is a unique order record and their are no duplicates.
I have a second table in my data model called Transactions - this table does not have any unique columns however it does have a column called 'OrderID' which is related to the orders stored in the 'Orders' table. A single OrderID may have multiple transactions against it.
I want to add a calculated column to the Orders table which sums the value of all the transactions in 'Transactions' which have a 'OrderID' equal to the 'OrderID' in 'Orders'
Before I was using PowerPivot this was simple, I used a SUMIFS function, this however is not available in PowerPivot, and building a relationship between the two tables does not work because their are multiple transactions for each Order.
Can anyone recommend I can achieve the same SUMIFS result in PowerPivot using a DAX syntax?
Thanks.