SOLVED

Summing (SUMIFS) with a Pivot Table and Manual Table. Easier Method?

Brass Contributor

I need assistance with making a process easier, if possible. 

 

I take raw data from a salary file, make a Pivot Table from it to sum it, I also have manual inputs for changes to personnel that affect the forecasted persons leaving (in the example I only use current months but real summary has future months for forecast), then I have a last built table that finally sums what came from the Pivot as actuals and my forecasted (manual input/typed in J2:N9 with formulas in yellow to automate for me).

 

I use SUMIFS in each one but this is sort of tedious to manage (I need more lines and then I have to fix the SUMIFS sum columns or other columns, and hope to be able to pivot this data in that final third table. Is there an easier way to do? I want to be able to sum these up quickly, Pivot it so it adds up correctly.

 

I thought about breaking the Pivot into one tab, the manual input into another, Pivot the manual input/forecasted, then sum both Pivots into one. I can't put the manual information in the original raw data because the format's are different and I'm given the data this way.

 

1.png

2.png

11 Replies

@Jpalaci1 

With Power Query:

- RawData as one source

- Inputs as another source with appropriate transformations (Year, Month, Monthly salary...)

- Aggregation of the RawData values

- Merged with the Inputs

- Sum of Aggregated RawData values + Input values

=> As source for Pivot Table

 

Updated example attached

@Jpalaci1 

On reflection another PQ option much easier:

- RawData as one source

- Inputs as another source with appropriate transformations (Year, Month, Monthly salary...) so the fields are the same as the RawData

- Combined the 2 tables

=> As source for Pivot Table

See attached file

Thank you for the response. Will try today if not tomorrow and report back.

Second question. In my example I simplified it. In my real model I have two manual tables. One does salary and second does taxes paid by company. Can it still work with using the original Pivot, my salary forecast, and then my second tax forecast manual table?
Re. 2nd Question. If you mean that you add up (currently with SUMIFS) Salary & Tax Paid, then in theory you can take the same approach

@L z. 

I have questions on how to complete what you did.

 

Did you just load the data or create a table (input table) from my manual inputs? Did you load into the data model or PQ?

 

My columns from my raw data and my manual inputs columns are not the same and not sure how I handle in Power Pivot like you did for the summary. How did you complete that?

Screen Shot 2021-07-10 at 2.57.40 PM.png

I needed to make a second taxes table to include for a second table. Since a new salary triggers a tax is it best to just have some how to see if there's a salary make a line that multiples by the 25% tax rate? I just want to be able to load into a single summary like you have because my real raw data has both salaries and taxes so I can switch around. I don't know how to do the same for the forecasted costs. For example, in the input table you have just automatically see it's a salary account and create a new line that does the salary*25%.

 

Thank you for any help.

 

@Jpalaci1 

Let's go 1st step at a time if you don't mind

1/ Nowhere I used Power Pivot but Power Query

2/ Look at sheet Inputs in the file I shared. I made a copy/paste of the relevant columns and rows (from your manual inputs in sheet Working Tab) and formatted the copied data as a Table that I named Inputs

3/ I "loaded" the existing table RawData in Power Query and did the same with the newly created Inputs table

4/ In Power Query I did a couple of transformations so the Inputs table looks the same as the RawData table

5/ I combined/appended tables RawDataInputs, then on exiting the Power Query Editor I chose option Pivot Table Report

 

So, above steps 3-5 are done in/with Power Query. Within Excel: Go to Data tab > Queries & Connections (this will open the corresponding pane on the right and you'll see:

Demo.png

> Double-click on i.e. Inputs and the Power Query Editor will open

 

And please, let's stay on the same page/picture => Use the file attached to this reply, it's an easier version of the 1st I posted

Thank you! I will attempt this tomorrow and get back. Thank you for all the help and going into the details.
Last question. I'm on the part to make the merge.
1) Do I make the merge as new or append as new? Which one?
2) Did you merge/append the raw data to the input or vice versa? I did both ways (after cleaning and matching columns) and only pulls the data in that table.
3) What was the primary key/foreign key? I chose company and didn't get what I needed.
4) What type of join? I did default Left Join.

Other than making the merge queries that's all I need.

Thank you.
best response confirmed by Jpalaci1 (Brass Contributor)
Solution

@Jpalaci1 

I would suggest you spend a few minutes reading Merge, Join, Append, Combine as nowhere in what I shared there's a merge between the 2 tables/queries, hence your questions

 

Assuming you used the same tables names in Excel (RawData & Inputs)

AppendQueriesThenPivotReport.png

 

And you can select Inputs as First table and RawData as Second table, that doesn't matter in this scenario

Thank you for the assistance. And thank you for providing me the details. I will go through this is detail.


Thank you for all the help. This fixed it. I apologize for my ignorance and thank you for showing me and you patience.

Glad I could help + Thanks for posting back
And don't apologize, we all started from scratch
Nice day...
1 best response

Accepted Solutions
best response confirmed by Jpalaci1 (Brass Contributor)
Solution

@Jpalaci1 

I would suggest you spend a few minutes reading Merge, Join, Append, Combine as nowhere in what I shared there's a merge between the 2 tables/queries, hence your questions

 

Assuming you used the same tables names in Excel (RawData & Inputs)

AppendQueriesThenPivotReport.png

 

And you can select Inputs as First table and RawData as Second table, that doesn't matter in this scenario

View solution in original post