Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Mar 14, 2023

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 

 

Excel
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Mar 14, 2023

    Tony2021 

    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)

     

     

     

Resources