# Sum the value of look up table based on two filter criteria

• ### Question

• Hello Everyone

I am new to Powerpivot and would appreciate if someone could help me on the following problem.

The first table is tOrders

 Week number Work center order number Production time in minutes 2 a 111 60 2 a 112 70 2 b 113 60 3 b 114 50 3 a 115 40 3 b 116 60 4 a 117 90 4 b 118 40

The second is dLookupList

 Week number Work center mantenace in minutes per week Break dows in minutes per week 2 a 10 10 2 b 20 5 3 a 15 12 3 b 30 10 4 a 20 10 4 b 10 10

I’m trying to create Pivot that has filter on Week number to show the number of orders, Sum of Production time in minutes and the total of the values form the lookup table dLookupList that matches the work center and the selected week numbers. So that I can calculate the total time for each work center.  Filter criteria is Week number and Work center.

For example if someone select all weeks numbers the result sould look like this

 Week number (All) Work center Count of order number Sum of Production time in minutes mantenace in minutes per week Break dows in minutes per week Total time a 4 260 45 32 337 b 4 210 60 25 295 Grand Total 8 470

Result for week 2

 Week number 2 Work center Count of order number Sum of Production time in minutes mantenace in minutes per week Break dows in minutes per week Total time a 2 130 10 10 150 b 1 60 20 5 85 Grand Total 3 190

How can I relate these two tables to get the above result?

Any help is highly appreciated.

Regards

Priyan

Monday, January 19, 2015 3:31 PM

• Hi Priyan,

I managed to solve your problem.

First thing i did was add a column in both of your tables so i could associate them with a unique code in order to relate the maintainance time per week since it variates every week.

after i added those columns i opened another excel file and went to powerpivot so i could associate both tables. Once you added the excel file to power pivot, you go to diagram and you associate it like this

• Proposed as answer by Tuesday, January 20, 2015 2:51 PM
• Marked as answer by Friday, January 30, 2015 9:30 AM
Monday, January 19, 2015 10:25 PM
• Once you have this done go to the excel in which you opened the powerpivot and insert a pivot table. You will be able to see all the columns from both of your tables
• Proposed as answer by Tuesday, January 20, 2015 2:51 PM
• Marked as answer by Friday, January 30, 2015 9:30 AM
Monday, January 19, 2015 10:26 PM
• And finally you will be able to have it like this Hopefully this is what you were looking for.

• Proposed as answer by Tuesday, January 20, 2015 2:51 PM
• Marked as answer by Friday, January 30, 2015 9:30 AM
Monday, January 19, 2015 10:27 PM

### All replies

• Hi Priyan,

I managed to solve your problem.

First thing i did was add a column in both of your tables so i could associate them with a unique code in order to relate the maintainance time per week since it variates every week.

after i added those columns i opened another excel file and went to powerpivot so i could associate both tables. Once you added the excel file to power pivot, you go to diagram and you associate it like this

• Proposed as answer by Tuesday, January 20, 2015 2:51 PM
• Marked as answer by Friday, January 30, 2015 9:30 AM
Monday, January 19, 2015 10:25 PM
• Once you have this done go to the excel in which you opened the powerpivot and insert a pivot table. You will be able to see all the columns from both of your tables
• Proposed as answer by Tuesday, January 20, 2015 2:51 PM
• Marked as answer by Friday, January 30, 2015 9:30 AM
Monday, January 19, 2015 10:26 PM
• And finally you will be able to have it like this Hopefully this is what you were looking for.

• Proposed as answer by Tuesday, January 20, 2015 2:51 PM
• Marked as answer by Friday, January 30, 2015 9:30 AM
Monday, January 19, 2015 10:27 PM
• Hi Recio

Thank you very much for the swift response. I was able to get it work.

I got two questions:

1. How do you add a total time column to the pivot table like you did? Because there are no calculated field in power pivot.
2. I prefer that the filter is based on the Orders table. So that if you select all Week numbers in the filter, that pivot will show result for all orders and relevant sums from the lookup list.

For example: I add Week number 5 to the work center “a”

 Week number Work center WNandWC mantenace in minutes per week Break dows in minutes per week 2 a WN2WCa 10 10 2 b WN2WCb 20 5 3 a WN3WCa 15 12 3 b WN3WCb 30 10 4 a WN4WCa 20 10 4 b WN4WCb 10 10 5 a WN5WCa 1 1

In the orders table there are no records for week number 5

 Week number Work center WNandWC order number Production time in minutes 2 a WN2WCa 111 60 2 a WN2WCa 112 70 2 b WN2WCb 113 60 3 b WN3WCb 114 50 3 a WN3WCa 115 40 3 b WN3WCb 116 60 4 a WN4WCa 117 90 4 b WN4WCb 118 40 4 a WN4WCa 119 50

But the pivot sums up the week number 5 also.

Do you have any idea how to solve it?

Thank you very much.

Regards

Priyan

Tuesday, January 20, 2015 8:44 AM
• Hi Priyan,

In order to solve your questions.

How do you add a total time column to the pivot table like you did? Because there are no calculated field in power pivot.

Its simple, the Total Time column isnt part of the pivot table. I only added a column there that sums the 3 values as you requested and I put it as the same color as the pivot table.

I prefer that the filter is based on the Orders table. So that if you select all Week numbers in the filter, that pivot will show result for all orders and relevant sums from the lookup list.

You just have to drag the value week number from the table Orders instead of lookupvalist. Also you will need to add row week 5a and week 5b to your lookuplist so it can keep filtering.

But yeah, just drag the column week numbers from orders instead of lookuplist and it will work

Tuesday, January 20, 2015 2:49 PM
• Hi Recio<o:p></o:p>

I would prefer to integrate totals to the pivot. <o:p></o:p>

The other thing is I would like to have one filter to select week number, rather than having two different filters to select. <o:p></o:p>

I think in this regards you do not have much flexibility in power pivot, isn’t it? <o:p></o:p>

For example, if made the same SQL joint in Access “week number” to “week number” and “work center” to “work center” in both tables. When I set the filter to week number 2 both data sets get filtered. <o:p></o:p>

Regards<o:p></o:p>

Priyanga<o:p></o:p>

Tuesday, January 20, 2015 3:53 PM
• Hi Priyan,

You can add a column in powerpivot which sums your 3 previous columns and then show it in your pivot table.

About week numbers im not sure if im following you but you have two filters because they exist in both of your tables orders and lookuplist. You dont need to use both filters, just the one from orders which is the one you wanted.

Could you make an example of the SQL? I don't have much experience with it but i've used it before