Forum Discussion
Summing (SUMIFS) with a Pivot Table and Manual Table. Easier Method?
I would suggest you spend a few minutes reading https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2010/power-query-get-transform-merge-join-append/83252b4a-785d-4e10-91dd-c887df283a65?tm=1589777580246 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)
And you can select Inputs as First table and RawData as Second table, that doesn't matter in this scenario
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
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
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?
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.
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 RawData & Inputs, 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:
> 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