Forum Discussion

SlyTokyo's avatar
SlyTokyo
Frequent Visitor
10 years ago

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  
Need Help
Tips and Tricks
asocorro's avatar
asocorro
Skilled Sharer
10 years ago

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:

 

 

  • SlyTokyo's avatar
    SlyTokyo
    Frequent Visitor
    10 years ago

    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?