PowerPivot model to calculate Retail Store Staff Incentives

[barnettjacob]

Hi Guys,


I'm trying to build a PowerPivot model to automate the process of calculating bonus payments to store staff members based on our monthly incentive scheme.


The scheme itself is pretty simple - there are 5 criteria that an employee must meet in order to be eligible to receive a payment which is determined by their grade.


The problem is that the vast majority of the conditions are at a store level not at the employee level. This means that when I write a measure to determine, say, whether the employee hit the sales target, it should be at the store level not the employee level.


Where I am running into difficulties is that unlike situations where I have done something similar in the past, using a simple 'ALL' in a calculate to remove the 'employee' context doesn't work in this case because the store and the employee are in different tables. The result is that although I the Store's IPS, I get it for every employee regardless of whether or not they work there.


Current formula:
=CALCULATE([IPS],(ALL('Staff Listing')))


The model contains 3 tables - 'Sales', 'Store Attributes' and 'Staff Listing' with both the 'Sales' and 'Staff Listing' related to the 'Store Attributes' table on the branchcode column.


If anyone has any ideas I would be very appreciative.


Jacob

 

[Laurent C]

Did I understand correctly that both the 'Store Attributes' table and the 'Staff Listing' tables are linked to the Sales table through the same key column?

If this is the case, then every row in your fact table will be associated with a single employee and a single store. That means you should not have to remove the employee context.

Where do you do your calculation (in a calculated column or in a measure)? Is [IPS] a measure or a column?