I have 3 tables. One is a table with employee IDs (Table 'ID'). One is a table with employee IDs and their 2017 sales for one business (Table 'A'). The third table has employee IDs and their 2017 sales for the other business (Table 'B'). Tables A and B are connected to the employee table by the employee ID. The employees are not exactly the same for each business, though there is some overlap where the employee ID could appear on A and B. I have columns on the ID table that are used as filters on the page, based on where the employee is located and how long they have been at the company.
I want to calculate the overlapping 2017 results. I have created a measure on the ID table that says this:
Overlap = if(SUM('A'[Sales])>0,SUM('B'[Sales]),0)
Basically: if the employee had Sales for A, give me their sales for B
It works for each ID on the table and the row shows 0 for the row if sales for A were 0, but the Grand Total returns the entire Sales for B (not just the sum of the sales for IDs that had more than 0 sales for A).
Any ideas on how to get the total to work correctly?
