locked
Sum the value of look up table based on two filter criteria RRS feed

  • 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:

    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:

    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.

    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