Active Member
July 16, 2020
Hello,
I have forecast and demand data for certain products for each month for 12 months. There hundreds of products, so for each month there are hundreds of rows with the forecast/demand data. I am calculating the difference between the actual demand data and the forecast data and wish to display a column that shows the total summed difference for each month. So, for all the products demanded in the month of 5/1/2015, the column will display the total difference between the forecast data and the actual demand for each product during that month. Any help would be appreciated. I will also gladly provide any additional needed information.
Thanks in advance!
Active Member
July 16, 2020
Hello Mynda,
I appreciate the response. However, I am interested in a solution that can be done within Power Query. I will provide a sample text example below.
period part demand forecast variance TOTAL VARIANCE BY MONTH
5/1/2015 A 10 9 1 4
5/1/2015 B 5 7 2 4
5/1/2015 C 5 6 1 4
6/1/2015 A 3 3 0 7
6/1/2015 B 8 2 6 7
6/1/2015 C 7 6 1 7
7/1/2015 A 7 2 5 14
7/1/2015 B 6 9 3 14
7/1/2015 C 4 10 6 14
I hope the formatting is clear. The column on the far right is the column I wish to generate within Power Query.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
It's not recommended to do that in PQ, it's better to make the aggregations in a pivot table, see image attached. The table you have is perfect for that.
If you still need that, use the query below:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"period ", type datetime}, {"part ", type text}, {" demand ", Int64.Type}, {"forecast ", Int64.Type}, {" variance ", Int64.Type}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([#"period "]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([#"period "]), Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Month",{{" variance ", "variance"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", (x)=> List.Sum(Table.SelectRows(#"Renamed Columns", each _[Year]=x[Year] and _[Month]=x[Month])[variance]))
in
#"Added Custom"
Answers Post
1 Guest(s)