cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThomasWeppler
Helper I
Helper I

Add multiple lookup values together

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.

1 ACCEPTED SOLUTION
ThomasWeppler
Helper I
Helper I

Thanks for asking.

 

I think I found a solution. I thoguht the problem was that I need an advanced version of the lookupfunction, but by useing the group-by function I managed to reduce the table size so I could use a regular lookup function.

If anyone else have a similar problem just search group-by and I am sure you will find the answer.

View solution in original post

2 REPLIES 2
ThomasWeppler
Helper I
Helper I

Thanks for asking.

 

I think I found a solution. I thoguht the problem was that I need an advanced version of the lookupfunction, but by useing the group-by function I managed to reduce the table size so I could use a regular lookup function.

If anyone else have a similar problem just search group-by and I am sure you will find the answer.

View solution in original post

v-alq-msft
Community Support
Community Support

Hi, @ThomasWeppler 

 

Could you please give us some explanations with some sample data and expected result for better understanding? Do mask sensitive data before uploading. Thanks.

 

Best Regards

Allan

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.