Answered by:
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.
You can download the example of this excel file form the following DropBox link:
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
Answers

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 recio Tuesday, January 20, 2015 2:51 PM
 Marked as answer by Charlie Liao 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 recio Tuesday, January 20, 2015 2:51 PM
 Marked as answer by Charlie Liao 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.
If it is please mark it as an answer
 Proposed as answer by recio Tuesday, January 20, 2015 2:51 PM
 Marked as answer by Charlie Liao 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 recio Tuesday, January 20, 2015 2:51 PM
 Marked as answer by Charlie Liao 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 recio Tuesday, January 20, 2015 2:51 PM
 Marked as answer by Charlie Liao 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.
If it is please mark it as an answer
 Proposed as answer by recio Tuesday, January 20, 2015 2:51 PM
 Marked as answer by Charlie Liao 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:
 How do you add a total time column to the pivot table like you did? Because there are no calculated field in power pivot.
 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.
Link download the example file
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
Please mark my replies as answers above if they helped you
Tuesday, January 20, 2015 4:19 PM 
Hi Rico<o:p></o:p>
I'll post the problem again. I think i need to present the broader picture.<o:p></o:p>
Regards<o:p></o:p>
Priyanga<o:p></o:p>
Tuesday, February 3, 2015 1:56 PM