This topic contains 2 replies, has 2 voices, and was last updated by 
-
Posts
-
I have ordered the book on M but still haven’t learned enough to be of any use. So, here I am again on the board asking for help making powerquery do my bidding.
I have a ‘Pod Members” table that I need to create a couple of calculations on. It has true duplicate entries that I need to remove in order to make the pivot table work but I know how to judge which entry to keep. I also need to calculate days in pod and, using that calculation create pod memberships.
I have attached a modified version of the table here. The names have been changed to protect the innocent.
Steps I need to create:
Determine current pod membership. If a team member is currently in the pod, the end date will be 12/31/9999. Delete duplicate member names that have left a pod. In the attached table there should be 3 examples of those duplicates.
Determine if the member is in training. A member is in training if their start date is less than 45 days from the current date. After 45 days, they are no longer in training and count in calculations as a pod member.
Create a method of calculating pod current pod membership to be used as a divisor in sales calculations. I need to be able to divide sales calls and sales dollars by the number of current pod members who are out of training. So, if a member is new, their calls and sales dollars count but the divisor is different. For example, JLENON is in the Sales Ninjas pod but has not been with us for 45 days. Therefore, the Sales Ninjas pod membership count should be 2, not 3.
As a wish list item, I would like to add a new column calculating the number of days spent in the new pod in case someone switched pods in the middle of a calculation period. My fear on this is how to keep them in two pods without creating duplicate entries that would negate table relationships. As a way to split sales calls and sales dollars between two pods if they changed to another.
Kristi,
The calculations you are interested in can be (depending on company policy and date range covered in a single report) either simple or complex.
What is missing for me to help you write formulas is whether your workbook’s Power Pivot model will cover only one (indivisible) reporting period or multiple. Here is a scenario: the basic reporting period is a month, but management wants to see the information spread out month-by-month over a year. In such a case, it is possible for a sales person to have filled the 45 day requirement in 2 (or more) pods.
The good news is that regardless of whether you are always working with one indivisible reporting period or with a date range covering multiple reporting periods, formulas can be written to handle either scenario. If reports will cover multiple periods, I request also sample sales data (that the contents are fictitious is not important, but the structure of the tables, columns and relationships are).
Please let me know which case applies to your report: (1) Always will be one indivisible reporting period or (2) will have multiple reporting periods (or will eventually have multiple reporting periods).
Tom
For now we are only looking at a single month but the hope is that we can look at more months as we get more adept at tracking this data.
For the training date, the reps do not train in each pod, only the first 45 days from First training date is considered training. After that, switching pods is just a matter of pod membership. If, after 40 days of training JBEIBER switches to another pod, only 5 more days of training will occur before becoming a full pod member.
And, just to keep it interesting, the monthly calculations will be cumulative, adding up from day 1 of the month so that each pod knows the current standings. Which, with Power Query, this is simply a matter of updating the tables to pull in additional rows. I just need to be able to update it on a daily basis through refresh.
The forum ‘Power Pivot’ is closed to new topics and replies.