Counting Number of Employees Not Included in Separate List

[ksimpkinson]

I have 2 tables loaded in Power BI, one using a normal import from a SQL Database, the other using a Direct Query from a SQL Database. The table loaded through normal import is made up of a combination of headcount and transaction data and includes information such as job title, action type, pay grade, department, etc. It looks something like this:

The second table is linked to the first one by an index and consists of various percent increases for variables such as promotion rate, hire rate, salary increase, etc. for different scenarios. For example, in one scenario, I may want Promotion rate for Sales to increase by 50%, and increase by 30% in Finance. This table would look something like this:

Not all data ID's from the first table will be included in the second table because not all scenarios will include all employees listed in the first table (i.e. IT was excluded from my example above).

I want to use this information to create forecasts. To calculate how many promotions there would be next year I would take the number of employees listed in the second table (in this case 8 ) and multiply it by the average of the promotion rates listed in the second table (in this case 1.4), then add the remaining promotions from the first table which didn't have a percent increase in their department (in this case 5 from IT). For my example this would be (8 * 1.4) + 5, which is 16.2.

Multiplying the number of promotions in the second table by the average of the promotions rates in the second table is the easy part, but I also would need to tally all other promotions that existed in the first table that weren't given a percent change and therefore aren't in the second table. In other words, how do I count all promotions from the first table who's data ID is not in the second table?

[Ashish_Mathur · Accepted solution]

Hi,

Write =RELATED('Table2'[Data ID]) calculated column formula in the first table.  Name this new column as Test.  Then try this measure

=calculate(countrows('table1'),'table1'[test]=blank())

Does this help?