Simple DAX two-filter question

[ramohse]

Hello!



I have a monthly contractor (vendor) report table that includes the following columns: Cost Center, Vendor Number, Amount Spent; I have a relationship with another table that links the cost center to a parent business unit. How would I write a DAX measure that sums the amount spent on each vendor by each business unit?

I can do grand total spent on each vendor:
=CALCULATE(SUM(TotalVendorLook[Amount]),ALLEXCEPT(TotalVendorLook,TotalVendorLook[Vendor #]))

but any time I try to filter the total amount spent by vendor BY parent business unit I get either an #ERROR or a sum of ALL the values, which ends up being much higher than the amount spent.


I am extremely new to all of this, and appreciate your help and patience.

 

[Rijnsent]

Hi ramohse,

I would add a column to your vendor report table with a simple VLOOKUP, showing the Parent Business Unit. Then you could use either a pivot table or a SUMIFS formula to get your totals. I hope that does the trick, if not, please post a link to a dummy file (on google drive/skydrive/dropbox).

Cheers,

Koen