Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
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.
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 🙂
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |