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
