Subtract values from columns in two tables with a filter

[leeh_lrems]

I have two tables, each containing "Check Number" as a key.   Table 1 (Earnings) has gross pay as a column.  Table 2 (Deductions) has all benefits costs as a column.  I'm having no luck in figuring out how to subtract deductions from earnings for each check number.  Any suggestions?

TIA - Lee

[Anonymous · Accepted solution]

Hi @leeh_lrems 

If there is a relationship between, you can try the following code as a column:

Subtract = var _sumcosts=CALCULATE(SUM(Deductions[benefits costs]),RELATEDTABLE(Deductions))
return [gross pay]-_sumcosts

Output:

If there is no relationship between two tables, you can refer to the following link as a column:

Sub = var _sumcosts=SUMX(FILTER('Deductions',[Check Number]=EARLIER('Earnings'[Check Number])),[benefits costs])
return [gross pay]-_sumcosts

Output:

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tc_WII]

I believe there are multiple ways to do it. I am sharing one option. Steps are given below:

1. Go to data modeling, and create relationship of two tables with the key

2. Go to visualization, Create a new measure

3. On the formula bar, simply write:

Net Pay = SUM(Earnings[Gross Pay]) - SUM(Deductions[Costs])

4. This will create a Measure named Net Pay (you can choose any name).

5. Create a table visualization. Choose the Check number from earnings table, and the new measure Net Pay.

It should work properly. Thank you 🙂