Forum Discussion
Filtering rows in one table by values from another table
I figured it out:
CALCULATE(sum(Table A[Total Cost]),EXCEPT(VALUES(Table A[Product_ID]),VALUES(Table B[Product_ID])))
I would just bring in one table. Merge Table B to Table A and create a Discontinued column. Then you can have very simple measures that just total revenue and costs with this:
Total Revenue = SUM('Table A'[Revenues])
Total Cost= SUM('Table A'[Cost])
See my PIBX attached. Note that in the Matrix settings I had to tick the Show the Values on the Rows setting. That is not the default. You can see in the Power Query (Transform Data) part where I merged the tables to just create a True/False field for the Discontinued. You can change that formulat to show "Discontinued" and "Not Discontinued" vs the true and false values there.
Thank you for your help. I'm not sure if that can work though. Table A has multiple instances of the ID that are added daily (the table is updated weekly), while Table B has one instance of each ID and changes (not necessarily all IDs) quarterly. Timing would also play a factor in how the discontinuation is applied. To provide a little more clarity, I used the term discontinuation, but really we offer different plans that provide for the ability to opt-out of specific products based on a given plan for reduced costs. This opt-out product list (plan) is generated quarterly. The goal of this analysis is to see the change in costs and revenues when a client wants to opt-out to determine overall pricing. The way that we calculate this is that we apply this opt-out plan to a clients previous product order history (Table A is total product orders) to tell them the savings from opting out. Accordingly, I would prefer to just update the data tables (Table A weekly and Table B quarterly), rather than having to re-create merged tables.
Here's a little more clarity via example:
Table A
| Order_date | Product_ID | Cost | Revenue |
1/1/2020 | 123 | 5 | 8 |
| 1/2/2020 | 111 | 7 | 15 |
| 1/3/2020 | 231 | 10 | 20 |
| 1/3/2020 | 222 | 3 | 5 |
| 1/4/2020 | 123 | 5 | 8 |
Table B
| Year | Quarter | Product_ID |
| 2020 | Q1 | 123 |
| 2020 | Q1 | 222 |
Output
| Total cost | Total revenue | |
| Opt-in | 30 | 56 |
| Opt-out | 17 | 35 |
I figured it out:
CALCULATE(sum(Table A[Total Cost]),EXCEPT(VALUES(Table A[Product_ID]),VALUES(Table B[Product_ID])))