I am somewhat new to powerBI with half a year of experience and I try to solve a problem where we need to analyse all tasks that are not finished yet and how much need to be expected to earn from them.
Colum names are underscored to make it easier to understand.
I have a table with multiple task each have an individual assingmentid and sales price. They also have a colum named open assingment, which has the value 1 if it is open and 0 if it has been closed. There are 3500 closed assignments and 500 open assignments.
Some of these assignments are subassingments and should be counted together with the main assignments.
If an assignment is a subassignemnt is has a parrentassignmentid which is the same as the assignmentid of the main assignment it is connected to. If an assingment is a main assignment the parrentassignmentid is null.
Multiple subassingments can have the same main assignment.
My questions are:
1. How do I add the sales price of the diffrent subassignments to the price of the main assingment?
2. I have another table with invoices lines. This table have a colum with an invoiceassingmentid which is the same as the asssingmentid from the task table and an invoiced value that shows how many money which has been colleted. How do I lookup the many invoiced values from diffrent subassingments and add them to the invoiced value from the main assingment?
The final product is gonna be a presentation which shows how much we are gonna earn from the task that are still to be closed, but haven't sent an invoice for yet.
All help and advice is greatly appreciated.
