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

2 Replies

  • 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?