Referencing/adding last item in multiple groups

%3CLINGO-SUB%20id%3D%22lingo-sub-1183289%22%20slang%3D%22en-US%22%3EReferencing%2Fadding%20last%20item%20in%20multiple%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1183289%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20data%20set%2C%20where%20I'm%20trying%20to%20report%20on%20time%20off%20running%20balances%20among%20departments%2C%20by%20month.%26nbsp%3B%20The%20system%20in%20which%20the%20data%20is%20being%20pulled%20automatically%20includes%20a%20running%20balance%20by%20transaction.%26nbsp%3B%20How%20do%20I%20summarize%20and%20reference%20the%20total%20balance%20by%20department%2C%20by%20month%2C%20when%20there%20are%20multiple%20employees%20in%20each%20department%3F%20I%20created%20a%20fake%20data%20set%20(attached)%20to%20hopefully%20explain%20what%20I'm%20talking%20about.%26nbsp%3B%20There%20must%20be%20a%20way%20to%20do%20this%20in%20a%20pivot%20table...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGoal%3A%3C%2FP%3E%3CP%3EJanuary%20values%20would%20equal%3A%3C%2FP%3E%3CP%3EMarketing%3A%2034.18%3C%2FP%3E%3CP%3EQuality%20Assurance%3A%20242.58%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFebruary%20values%20would%20equal%3A%3C%2FP%3E%3CP%3EMarketing%3A%208.34%3C%2FP%3E%3CP%3EQuality%20Assurance%3A%20255.30%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1183289%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1183890%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%2Fadding%20last%20item%20in%20multiple%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1183890%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F563585%22%20target%3D%22_blank%22%3E%40cdolan27%3C%2FA%3E%26nbsp%3BBased%20on%20your%20question%20alone%2C%20I%20assume%20that%20you%20want%20to%20summarise%20transactions%20by%20department%2C%20by%20month.%20A%20simple%20pivot%20table%20can%20indeed%20achieve%20this%2C%20as%20demonstrated%20in%20the%20attached%20file.%20But%2C%20I%20am%20unable%20to%20recreate%20all%20the%20numbers%20you%20expect%20to%20see.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMarketing%3A%3C%2FP%3E%3CP%3EJanuary%20is%20OK%2C%20but%20the%20February%20amount%20represents%20the%20sum%20of%20transactions%20through%20February%20(i.e.%20Jan%20%2B%20Feb)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuality%20Assurance%3A%3C%2FP%3E%3CP%3EEither%20of%20the%20amounts%20you%20mentioned%20match%20the%20summary%20in%20the%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20is%20not%20what%20you%20had%20in%20mind%2C%20you%20need%20to%20clarify%20how%20you%20calculated%20your%20numbers%20from%20the%20data%20set%20that%20you%20provided.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1183991%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%2Fadding%20last%20item%20in%20multiple%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1183991%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F563585%22%20target%3D%22_blank%22%3E%40cdolan27%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20file%2C%20I%20managed%20to%20get%20the%20solution%20done%20by%20Power%20Query%2C%20You%20can%20see%20the%20steps%20in%20the%20file%20%26amp%3B%20follow%20the%20same%20with%20your%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_b9274a.png%22%20style%3D%22width%3A%20808px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172182i46AE2E24BD8E1D8E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Snag_b9274a.png%22%20alt%3D%22Snag_b9274a.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%20%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.excelexciting.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.ExcelExciting.com%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1184574%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%2Fadding%20last%20item%20in%20multiple%20groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1184574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3Bthank%20you!%26nbsp%3B%20I%20haven't%20used%20Power%20Query%20before%2C%20so%20I%20have%20a%20bit%20of%20research%20to%20do%20on%20my%20end.%26nbsp%3B%20I%20didn't%20see%20the%20steps%20in%20the%20file%3F%26nbsp%3B%20Or%2C%20where%20can%20I%20find%20them%3F%26nbsp%3B%20Thank%20you!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have a data set, where I'm trying to report on time off running balances among departments, by month.  The system in which the data is being pulled automatically includes a running balance by transaction.  How do I summarize and reference the total balance by department, by month, when there are multiple employees in each department? I created a fake data set (attached) to hopefully explain what I'm talking about.  There must be a way to do this in a pivot table...

 

Goal:

January values would equal:

Marketing: 34.18

Quality Assurance: 242.58

 

February values would equal:

Marketing: 8.34

Quality Assurance: 255.30

2 Replies
Highlighted

Hi @cdolan27 

 

Please see the attached file, I managed to get the solution done by Power Query, You can see the steps in the file & follow the same with your data. 

 

Snag_b9274a.png

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com

Highlighted

@Faraz Shaikh thank you!  I haven't used Power Query before, so I have a bit of research to do on my end.  I didn't see the steps in the file?  Or, where can I find them?  Thank you!!