Forum Discussion
Tony2021
Mar 14, 2023Steel Contributor
Show a Variance
Hello Experts,
I have data grouped by month ([Amount] and [Month].
I am wanting to show a variance per month between 2 sheets: Forecast and the Actual.
My problem is that one of the data sets (Actual) is a pivot table and is not setup like the other data set (Forecast).
I am not sure how best to handle this and think an expert would know how to adjust it.
I am not sure if a pivot table is best to show the variance.
Please see attached file.
Grateful for the help
Revised workbook attached.
7 Replies
Sort By
- Patrick2788Silver Contributor
I hope I'm interpreting this request correctly (If you're looking to use VAR, the solution can be adjusted).
My solution summarizes the actual data by month (Pivot not used) and then does: Actual - Forecast
'Header - an array constant ={"Date", "Variance"} 'Months - a unique list of months from the actual table. Properly formatted. =UNIQUE(TEXT(tblActual[Funding Date], "mmm-y")) 'Lambda 'Diff' - it obtains the forecast for a given month by looking up the unique date in the forecast table. It obtains the actual amount by filtering the actual table by that same unique date. SUM the results to reduce to 1 value. Finally, HSTACK stack the date, and the result of Actual-Forecast. =LAMBDA(a,v,LET( fcast, XLOOKUP(v, tblFC[#Headers], tblFC), actual, SUM(FILTER(tblActual[T. Amt of Draw], TEXT(tblActual[Funding Date], "mmm-yy") = v)), VSTACK(a, HSTACK(v, actual - fcast)) )) Sheet level formula: =REDUCE(Header,Months,Diff)
- Tony2021Steel ContributorWow. thanks Patrick. That way beyond my skill level. Would you possibly be able to add the sum of the months for the Forecast and the Actual within the variance sheet?
- Patrick2788Silver Contributor