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...
- Mar 14, 2023
Revised workbook attached.
Patrick2788
Mar 14, 2023Silver 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)
Tony2021
Mar 14, 2023Steel Contributor
Wow. 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?
- Patrick2788Mar 14, 2023Silver Contributor
Revised workbook attached.
- Tony2021Mar 14, 2023Steel Contributor
Hi Patrick, that is really cool. I failed in my explanation though. Sorry. I meant can the Actual and Forecast numbers be included in the variance output? Basically, what I am looking for is to insert the Forecast amt in Col B and Actual Amt in Col C and Variance in Col D. I would not need the Total column actually. I would try to figure it out but it's far beyond my knowledge. The reason I need the Forecast and Variance numbers in the variance output sheet is because I plan to make a graph.
Thank you very much and once again apologies on the confusion. I hope its clear now. Kindly let me know if not.
- Patrick2788Mar 15, 2023Silver ContributorI've updated my previous post with the revision.