January 3, 2016 at 12:08 pm #2788
- Started: 1
- Replies: 0
- Total: 1
I am new to powerpivot and getting stuck with this problem. In the attached file, I have incentive calculation done- basis idea is that we need to calculate incentive for a bunch of sales people- they have “maintenance” figures ( basically last years sale that must be beaten to get a commission), Budget ( or target ) figures and actuals. The calculation is done on a quarterly basis ( I provided July, Aug, Sept figures that are basically q3 or Quarter 3 figures). Each month, actual fig is more than maintenance for that particular month, they get a maintenance fees ( otherwise 0). For the total quarter, if total actual is more then maintenance, they get 8% commission on growth(= Actual -maintenance, if more than 0), but if they achieve the budget/target fig, they get 12.5% commsion. I have the excel calculation in the calculation tab. How do I do this calculation thru powerpivot? In the actual flat files, I have the months horizontically presented on top but I thought that PV would not be able to grasp that, so I put them in columnar way such that employee numbers and months are appearing in columns but I am having many to may relationships and pivot is not working. Could somebody help?
Attachments:You must be logged in to view attached files.January 4, 2016 at 5:25 am #2790
- Started: 7
- Replies: 2552
- Total: 2559
You are a brave soul to try this one as a newbie.
To replicate your layout requires a cube formula report.
Attached is an Excel 2013 workbook (will not work with 2010) with a power pivot model and a cube formula report. If you have Excel 2010, let me know and I will attach a 2010 workbook in a following comment.
If you had only taken the Comprehensive Power Pivot Course you might have been able to nail this one down without much ado (sigh…). When you follow the link, feel free to choose the Preview option (there is no charge for the preview).
If the model blows your mind (I get around the many-to-many by using 3 data tables and two lookup tables), the Comprehensive Power Pivot Course also teaches how you could have done that (and much, much more).
Seriously, check it out. When you sign up, you can even post questions on course topics to an instructor.
Attachments:You must be logged in to view attached files.
You must be logged in to reply to this topic.