locked
COMPARE ACTUALS WITH BUDGETS IN POWER PIVOT RRS feed

  • Question

  • Hi experts

    I have created a power pivot to compare actual against budget , by sales person. For the budget I have created a BUDGET CODE for each employee by months . 

    In the Data model I have a table for ACTUAL sales by Sales person with a BUDGET CODE in every transaction for that employee. that is employees Budget to measure against actual.

    1. In the BUDGET I have BUDGET CODE & $ values & other details by month. (also, sales persons names against each BUDGET CODE)
    2. to link above 2 I have created a table with budget codes.  (to create relationship)

    If sales person X leave in April , how can I allocate the rest of the budget to new sales person Y? I prefer not to update Y's details in the budget but use the BUDGET CODE to do this in the power pivot.

    How can I achieve this?

    thanks .

     

    Saturday, May 30, 2015 8:19 AM

Answers

  • Hi,

    If my understanding is right you want to allocate the budget to a job/position in a company instead of an individual whose key you’re now using in your datamodel.

    That shouldn’t be a problem: Just add an additional column in your employees table (if it’s not there already): job/position and take this as your field into your reports.

    It’s basically as if you’re reporting on a (very fine) group level instead of on detail level.

    So if you want to see the employees name in that report you have 2 choices:

    1. Drag the existing detail field that contains the employees name into the report as well: Your subotals will then show the continuous row of budgeted months, in the detail fields you will be able to spot the change of employees.
    2. Instead of filling the (new) column Job/Position with the anonymized description of the job, fill in the employees name and where you have more than 1 employee you write: “employee1, followed by employee2”. Then you can stick to 1 line only in your report.

    However: All these solutions will cut the unambiguousness between budget and employee which might be needed at other occasions (i.e. commission or bonus calculations)!


    Imke

    • Proposed as answer by Michael Amadi Sunday, May 31, 2015 4:38 PM
    • Marked as answer by Charlie Liao Monday, June 8, 2015 8:58 AM
    Saturday, May 30, 2015 9:38 AM
    Answerer