Tricky Pivot Table Analysis

S

soopafly81

New Member
Joined
Jan 29, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi everyone! First time poster here after attempting to tackle my issue over several days.

I've included a range of sample data below to help understand what I am trying to achieve.

  • I have a list of case numbers (column A) which are duplicated because individuals log separate instances of time to those cases (columns F - I).
  • I need my information organized by month which I will use the Date/Time Opened (column C)
  • This is where it gets tricky:
    • If for the month of December 2020, a person's name appears in the Assigned To field, I need to count the distinct number of case numbers for which they will get credit for working on so I can see the total number of cases worked in a month
    • I also need to count the # of minutes from column G for only the time that was created by a specific individual and calculate the total time, independent of whether they were the Assigned To individual or not.
  • For example, in the sample data set, Bonnie was assigned to 2 cases, so 2 would be the total.
    • However, Bonnie has case time on other cases she wasn't the Assigned To person, which would need to add to her total of Time Spent (Min) which for this data set equals 75.
Is there a different way to filter to capture what I am looking for? Filtering Bonnie on Assigned To excludes all of her other case time spent on other cases.

bonnie.png




report1611928294552.xls
ABCDEFGHI
1Case NumberCase OwnerCase Close DateDate/Time OpenedAssigned ToCase Time: Record TypeCase Time: Created ByCase Time: Created DateTime Spent (Min)
200047563Stephanie1/4/2021 7:1412/31/2020 16:37Stephanie02 - Case WorkStephanie1/4/20217
300047558John12/31/2020 16:06John02 - Case WorkBonnie1/27/202110
400047558John12/31/2020 16:06John02 - Case WorkBonnie1/27/202115
500047558John12/31/2020 16:06John02 - Case WorkJohn1/27/202130
600047557Gary1/4/2021 9:4612/31/2020 16:02Gary02 - Case WorkGary1/4/202130
700047556Linda1/6/2021 9:2312/31/2020 16:02Linda02 - Case WorkLinda12/31/20204
800047555Laura1/22/2021 8:3912/31/2020 15:52Laura01 - Case ManagementLaura1/4/202120
900047550Stephanie12/31/2020 15:09Stephanie01 - Case ManagementStephanie1/27/202115
1000047550Stephanie12/31/2020 15:09Stephanie01 - Case ManagementStephanie1/20/202110
1100047549Stephanie1/5/2021 7:0412/31/2020 15:02Stephanie02 - Case WorkStephanie1/4/202110
1200047546Kristy1/5/2021 15:4712/31/2020 14:40Kristy01 - Case ManagementKristy12/31/2020180
1300047546Kristy1/5/2021 15:4712/31/2020 14:40Kristy02 - Case WorkKristy1/5/2021165
1400047546Kristy1/5/2021 15:4712/31/2020 14:40Kristy02 - Case WorkKristy1/5/202125
1500047543Dina1/5/2021 11:1412/31/2020 14:05Dina02 - Case WorkDina1/4/202190
1600047543Dina1/5/2021 11:1412/31/2020 14:05Dina02 - Case WorkDina1/5/202145
1700047543Dina1/5/2021 11:1412/31/2020 14:05Dina01 - Case ManagementDina1/4/202110
1800047538Gary12/31/2020 14:4612/31/2020 12:09Gary02 - Case WorkGary12/31/202045
1900047537Linda1/7/2021 11:0412/31/2020 11:58Linda02 - Case WorkLinda1/4/202130
2000047531Kristy1/4/2021 10:2112/31/2020 11:10Kristy01 - Case ManagementKristy12/31/202010
2100047531Kristy1/4/2021 10:2112/31/2020 11:10Kristy01 - Case ManagementKristy1/4/20213
2200047526Jessica1/22/2021 13:1912/31/2020 10:44Jessica01 - Case ManagementJessica12/31/20205
2300047518Gary1/4/2021 15:3312/31/2020 10:21Gary02 - Case WorkSusan1/4/202145
2400047515Laura1/4/2021 9:0012/31/2020 10:01Laura02 - Case WorkLaura12/31/20208
2500047514Laura1/4/2021 8:4712/31/2020 9:58Laura02 - Case WorkLaura12/31/202015
2600047512Linda1/7/2021 13:3312/31/2020 9:39Kenneth01 - Case ManagementKenneth1/6/202110
2700047512Linda1/7/2021 13:3312/31/2020 9:39Kenneth02 - Case WorkKenneth1/6/2021115
2800047512Linda1/7/2021 13:3312/31/2020 9:39Kenneth02 - Case WorkKenneth1/7/202135
2900047512Linda1/7/2021 13:3312/31/2020 9:39Kenneth01 - Case ManagementKenneth1/7/20215
3000047508Dina12/31/2020 9:0712/31/2020 8:51Dina01 - Case ManagementDina12/31/202010
3100047479John1/25/2021 12:3012/30/2020 20:49Christine02 - Case WorkChristine1/7/202165
3200047478John1/25/2021 12:3112/30/2020 20:48Christine02 - Case WorkChristine1/7/2021110
3300047471John12/31/2020 16:2412/30/2020 19:08John02 - Case WorkJohn12/31/202010
3400047468Dina1/4/2021 10:4312/30/2020 17:48Dina02 - Case WorkDina1/4/20215
3500047468Dina1/4/2021 10:4312/30/2020 17:48Dina01 - Case ManagementDina12/30/2020
3600047466Stephanie1/4/2021 12:5912/30/2020 17:33Stephanie02 - Case WorkStephanie1/4/202115
3700047464Linda1/7/2021 10:5412/30/2020 17:12Linda02 - Case WorkLinda1/4/202130
3800047462Lindsay12/30/2020 16:56Lindsay02 - Case WorkLindsay1/28/202130
3900047462Lindsay12/30/2020 16:56Lindsay02 - Case WorkLindsay1/28/202190
4000047462Lindsay12/30/2020 16:56Lindsay02 - Case WorkLindsay1/19/202175
4100047462Lindsay12/30/2020 16:56Lindsay02 - Case WorkLindsay1/18/202145
4200047461Stephanie12/31/2020 13:0412/30/2020 16:52Stephanie01 - Case ManagementStephanie12/30/20205
4300047460Kristy1/5/2021 10:0812/30/2020 16:49Kristy01 - Case ManagementKristy12/31/20203
4400047460Kristy1/5/2021 10:0812/30/2020 16:49Kristy01 - Case ManagementKristy12/31/202010
4500047460Kristy1/5/2021 10:0812/30/2020 16:49Kristy01 - Case ManagementKristy1/4/20213
4600047458John12/30/2020 16:46John02 - Case WorkBonnie1/27/202115
4700047458John12/30/2020 16:46John03 - Case CheckBonnie1/27/202110
4800047456Kristy1/5/2021 10:0812/30/2020 16:40Kristy01 - Case ManagementKristy12/31/20203
4900047456Kristy1/5/2021 10:0812/30/2020 16:40Kristy01 - Case ManagementKristy12/31/202010
5000047456Kristy1/5/2021 10:0812/30/2020 16:40Kristy01 - Case ManagementKristy1/4/20213
5100047453Laura1/7/2021 9:0712/30/2020 16:12Laura02 - Case WorkLaura1/4/202120
5200047452Dina1/4/2021 11:0112/30/2020 16:12Dina01 - Case ManagementDina12/30/20205
5300047452Dina1/4/2021 11:0112/30/2020 16:12Dina02 - Case WorkDina1/4/202115
5400047449Gary12/30/2020 20:0412/30/2020 16:02Gary02 - Case WorkGary12/30/202020
5500047444Linda1/4/2021 10:3012/30/2020 15:37Linda02 - Case WorkLinda1/4/202130
5600047440Lindsay12/30/2020 15:17Lindsay02 - Case WorkLindsay1/28/202160
5700047440Lindsay12/30/2020 15:17Lindsay02 - Case WorkLindsay1/28/202170
5800047440Lindsay12/30/2020 15:17Lindsay02 - Case WorkLindsay1/18/202145
5900047433Gary12/31/2020 9:5612/30/2020 14:40Gary01 - Case ManagementGary12/31/20205
6000047430Lindsay1/4/2021 11:2212/30/2020 14:26Lindsay02 - Case WorkLindsay12/31/20205
6100047429Dina12/30/2020 20:4912/30/2020 14:26Dina01 - Case ManagementDina12/30/20205
6200047427John1/25/2021 12:3012/30/2020 14:22Christine02 - Case WorkChristine1/22/2021180
6300047427John1/25/2021 12:3012/30/2020 14:22Christine02 - Case WorkChristine1/7/2021110
6400047415Laura1/4/2021 11:4412/30/2020 12:33Laura02 - Case WorkLaura12/31/202030
6500047414Gary12/31/2020 14:4612/30/2020 12:31Gary02 - Case WorkGary12/31/202010
6600047405Linda1/7/2021 16:0712/30/2020 11:31Linda02 - Case WorkLinda1/7/202115
6700047402Gary12/30/2020 19:4812/30/2020 11:18Gary02 - Case WorkGary12/30/202020
6800047399John12/30/2020 13:5412/30/2020 11:09John02 - Case WorkJohn12/30/202030
6900047397Dina1/21/2021 12:3112/30/2020 10:58Dina02 - Case WorkDina1/6/20215
7000047397Dina1/21/2021 12:3112/30/2020 10:58Dina01 - Case ManagementDina12/30/202020
7100047397Dina1/21/2021 12:3112/30/2020 10:58Dina01 - Case ManagementDina1/6/20215
7200047397Dina1/21/2021 12:3112/30/2020 10:58Dina01 - Case ManagementDina1/21/20215
7300047393Bonnie12/31/2020 11:1912/30/2020 10:43Bonnie02 - Case WorkBonnie12/31/202015
7400047387Dina1/5/2021 15:0712/30/2020 10:01Adele02 - Case WorkAdele1/5/202160
7500047387Dina1/5/2021 15:0712/30/2020 10:01Adele02 - Case WorkAdele1/5/20215
7600047387Dina1/5/2021 15:0712/30/2020 10:01Adele02 - Case WorkAdele1/5/20215
7700047387Dina1/5/2021 15:0712/30/2020 10:01Adele02 - Case WorkAdele1/5/20215
7800047387Dina1/5/2021 15:0712/30/2020 10:01Adele02 - Case WorkAdele12/30/202030
7900047382Gary12/30/2020 10:2512/30/2020 9:24Gary02 - Case WorkGary12/30/202015
8000047381John12/30/2020 13:5512/30/2020 9:23John02 - Case WorkJohn12/30/202010
8100047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika01 - Case ManagementStephanie12/30/20207
8200047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika02 - Case WorkErika1/6/2021180
8300047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika02 - Case WorkErika1/6/2021165
8400047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika01 - Case ManagementStephanie1/7/20215
8500047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika02 - Case WorkErika1/4/2021210
8600047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika02 - Case WorkErika1/8/202140
8700047372Stephanie1/8/2021 10:1612/30/2020 8:26Erika02 - Case WorkErika1/1/202175
8800047364Linda12/30/2020 9:2512/30/2020 6:30Bonnie02 - Case WorkBonnie12/30/202010
report1611928294552
 
Sort by date Sort by votes
Power Query: 
// Query1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Case Number", type datetime}, {"Case Owner", type text}, {"Case Close Date", type datetime}, {"Date/Time Opened", type date}, {"Assigned To", type text}, {"Case Time: Record Type", type text}, {"Case Time: Created By", type text}, {"Case Time: Created Date", type date}, {"Time Spent (Min)", Int64.Type}}),
    Group1 = Table.Group(Type, {"Case Time: Created By", "Date/Time Opened"}, {{"Sum", each List.Sum([#"Time Spent (Min)"]), type number}}),
    Filter1 = Table.SelectRows(Group1, each ([#"Case Time: Created By"] = "Bonnie")),
    Group2 = Table.Group(Type, {"Case Time: Created Date", "Assigned To"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
    Filter2 = Table.SelectRows(Group2, each ([#"Case Time: Created Date"] = #date(2020, 12, 30) or [#"Case Time: Created Date"] = #date(2020, 12, 31))),
    Join = Table.NestedJoin(Filter2,{"Case Time: Created Date"},Filter1,{"Date/Time Opened"},"Table",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Table", {"Sum"}, {"Sum"}),
    Filter = Table.SelectRows(Expand, each ([Assigned To] = "Bonnie"))
in
    Filter
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
You must log in or register to reply here.

Similar threads

H
  • Solved
Search for topics based on their groups.
Replies
7
Views
132
Peter_SSs
Peter_SSs
M
  • Question
Best solution to consolidate values that occur on multiple dates/lines
Replies
5
Views
263
Peter_SSs
Peter_SSs
H
  • Solved
Transpose data.
Replies
4
Views
99
harzer
H
M
  • Solved
Having issues with a IF(MAX(INDEX formula returning Yes or No
Replies
3
Views
81
RoryA
RoryA
N
  • Question
Pivot Table Total
Replies
5
Views
237
neasds
N

Forum statistics

Threads
1,214,606
Messages
6,120,497
Members
448,967
Latest member
visheshkotha

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top