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
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
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
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.
