DAX formula to sum a column from another table based on multiple filters in the main table

[Rune_]

I have 3 tables:

• JobHeader
• JobTask
• JobPlanningLine

I want to create a table in Power BI that shows Job No and Job Task No from the JobTask table and sums the PlannedSales column from the JobPlanningLine table.

In the JobTask table, for each Job No and Job Task No, there is a sum of different Job Task No called Totaling. So, Job Task No = 199 should be a summary of Totaling == 100..199.

In the JobPlanningLine table, each row contains Job No and JobTaskNo where, for example, it's between 100-198.

I have split the Totaling column into two columns named Totaling.1 and Totaling.2. For example, 100..199 becomes 100 and 199.

Also The LineType should be either 1 or 2. 

I think the formula should be something like: Sum PlannedSales for Job No and Job Task No >= Totaling.1 and <= Totaling.2 and LineType = 1 or 2

The JobTask table should be the main table I work from since I need to perform similar operations on many tables.

I have attached a screenshot of the relationships between tables and an Excel file with sample data.

Relationship between Job No

How I want it in PowerBi

Link to

Test Data <--- LINK

Job No	Status
10100	Open
10101	Quote
10102	Invoiced
10103	Invoiced
10104	Open
10105	Quote
10106	Invoiced
10107	Open
10108	Quote

Job No	Job Task No	Totaling	Totaling.1	Totaling.2
10100	199	100..199	100	199
10100	299	200..299	200	299
10100	399	300..399	300	399
10101	199	100..199	100	199
10101	299	200..299	200	299
10101	399	300..399	300	399
10102	199	100..199	100	199
10102	299	200..299	200	299
10102	399	300..399	300	399

Job No	Job Task No	Line Type	PlannedSales
10100	110	0	0
10100	120	1	100
10100	130	2	5000
10100	140	1	100000
10100	141	2	50000

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...