I hope you can help me with this requirement; I create two pivot tables, the first one is showing the backorder per product and sales channel, the second pivot table is showing the over sales per product and sales channel as well; The information I have in these two tables is
The table I’m looking for should be something like this
The information will change per month and year based on user’s selection, knowing that, I cannot set sales channel as fixed in the third table:
The information from these two tables is being shown in a waterfall graph
The first bar is showing 217 (following the example of the first table related to BackOrder), the second bar is showing 192 (related to the second table for Over Sales) and the third bar should be showing 37 since we are looking the difference between these two numbers only for those cases in which the products and the sales channel coincide.
I tried using that expression but in my example the reusult was 25 (217 - 192 = 25), and what I'm looking for is using Expr 1 - Expr 2 but only for those products and sales channels that exists in both pivot tables, in my example should be product B and D in combination with Sales Channel 2 and 3, in that case the result should be 217 - 180 = 37.
The only idea that comes to mind here is to use some sort of flag field that could be used in the expressions to get the 'matched' values of which you spoke ignoring those that do not match, best idea that popped into my head, maybe someone else will be able to expand upon it for you.
To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question. I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.