Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Variance vs. Budget

Dear All,


I want to calculate the variance 2016 vs. BU 2016 with a database like below.
What is the most efficient way to proceed? thank you for your support !! Sly



asocorro Continued Contributor
Continued Contributor

Re: Variance vs. Budget

One way to do it is to add another value column that would be a lookup to get the matching BU 2016 value for each 2016 value:


Compare Value = LOOKUPVALUE(Sheet1[Value],[Year],"BU 2016",[Month],[Month],[Category],[Category])


And then a second column for the difference:


Difference = Sheet1[Value]-Sheet1[Compare Value]


For example:




Then you would filter your visual to display only 2016 values, and get this:




Connect with me in LinkedIn:
Follow me on Twitter:
Frequent Visitor

Re: Variance vs. Budget

Thank you very much for your answer.
Unfortunatelly, my database is more detailled as I have another column called "sub category" wih the name of all the employees...
This subcategory is changing every month (= people leaving or joining the company) so the matching is not possible.

Any other ideas?


Helpful resources

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.