Forum Discussion
Solved
Help: Calculated column only considering half of filter condition
Hello, I'm having trouble with a DAX expression. I have two tables, Vendors and Receipts . They're laid out like so, with a many-to-many relationship linked through the Vendor column:
Vendors:
| Month | Vendor |
| 2022-01 | AlphaAssociates |
| 2022-02 | AlphaAssociates |
| 2022-01 | BuenoBiz |
| 2022-02 | BuenoBiz |
| 2022-01 | CenturyComm |
| 2022-02 | CenturyComm |
Receipts:
| Month | Vendor | Cost |
| 2022-01 | AlphaAssociates | $10 |
| 2022-01 | AlphaAssociates | $5 |
| 2022-01 | BuenoBiz | $5 |
| 2022-01 | CenturyComm | $7 |
| 2022-02 | AlphaAssociates | $15 |
| 2022-02 | BuenoBiz | $9 |
| 2022-02 | CenturyComm | $3 |
I'm trying to add a calculated column to the Vendors table that sums the data in Receipts according to the Month and Vendor of the row. Right now I'm using the expression
Total Cost = CALCULATE( Sum('Receipts'[Cost]), FILTER('Receipts',Receipts[Vendor] = [Vendor] && Receipts[Month] = [Month]))
I expect my Vendors table to look like:
| Month | Vendor | Cost |
| 2022-01 | AlphaAssociates | $15 |
| 2022-02 | AlphaAssociates | $15 |
| 2022-01 | BuenoBiz | $5 |
| 2022-02 | BuenoBiz | $9 |
| 2022-01 | CenturyComm | $7 |
| 2022-02 | CenturyComm | $3 |
But instead I get all matching vendors summed, regardless of month:
| Month | Vendor | Cost |
| 2022-01 | AlphaAssociates | $30 |
| 2022-02 | AlphaAssociates | $30 |
| 2022-01 | BuenoBiz | $14 |
| 2022-02 | BuenoBiz | $14 |
| 2022-01 | CenturyComm | $10 |
| 2022-02 | CenturyComm | $10 |
What am I missing here? Or is this not feasible without a helper column of "Month&Vendor"?
ekepp , I tried a new column and getting the correct result
Column = sumx(FILTER(Receipts, Receipts[Month] = Vendors[Month] && Receipts[Vendor] = Vendors[Vendor]),Receipts[Cost])
2 Replies
- amitchandak
Super User
ekepp , I tried a new column and getting the correct result
Column = sumx(FILTER(Receipts, Receipts[Month] = Vendors[Month] && Receipts[Vendor] = Vendors[Vendor]),Receipts[Cost]) I guess I should have been using SUMX instead of CALCULATE and SUM, thank you!
