Referencing/adding last item in multiple groups

Copper 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

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

@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!!