cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SlyTokyo
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

 

data.png

2 REPLIES 2
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:

 

Difference.jpg

 

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

 

s.jpg

 

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
SlyTokyo
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

Announcements
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.