PowerPivot - Productivity Per Person Per Day

[shane_aldrich]

Doing my best to simplify the question…apologies up front…I’m just starting out with PowerPivot
I have a PowerPivot with 4 tables…

FactTable1 (relationship with FactTable2 based on COMBO field, relationship with calendar based on date)
FactTable2 (relationship with FactTable1 based on COMBO field) – described below
CALENDAR (relationship with FactTable1 based on date, relationship with sales days based on date)
SALESDAYS – described below

FactTable2 – Description - each person will be listed multiple times, but only once for each month)

Last Day of Month = related(FactTable1[LastDayOfMonth]
Combo = text string…name sales person & “YYYYMM”
HeadcountWeek1 = If( SalesWeek1>0,1,0)
HeadcountWeek2 = If( SalesWeek2>0,1,0)
HeadcountWeek3 = If( SalesWeek3>0,1,0)
SalesWeek1 = count of sales for week1
SalesWeek2 = count of sales for week2
SalesWeek3 = count of sales for week2

SALESDAYS - has 24 months listed

LastDayOfMonth
SalesDaysWeek1 = count of sales days in the week (can vary by month depending on holiday)
SalesDaysWeek2 = count of sales days in the week (can vary by month depending on holiday)
SalesDaysWeek3 = count of sales days in the week (can vary by month depending on holiday)

I’m trying to solve for Sales Per Person Per Sales Day aka “ProductivityPerDayWeek1”

Within the FactTable2, I have calculated fields for SalesPerAgent for each week…below is an example
SALES_PER_PERSON_WEEK_1:=DIVIDE(SUMX(FactTable2,[SALES_WEEK_1]),SUMX(FactTable2,[HEADCOUNT_WEEK_1]))

So in order to get “ProductivityPerDayWeek1”, I need to divide SALES_PER_PERSON_WEEK_1 by the SalesDaysWeek1 for the particular month

Hoping I’m a little clearer than mud…thank you in advance for your help!!!

 

[scottsen]

I suggest you rework your Calendar/Date table to include the concept of "Is Sales Day" -- it's going to make all this easier.

 

[shane_aldrich]

Hi guess I'm not following you...I attempted to go about this a few different ways...changing relationships, and changing the way I'm attempting to calculated, but w/o much luck so far...


Can you be more specific / offer more guidance?

 

[scottsen]

Ya know what? If you get this today, drop me an email. I'll give you an hour of my time, we will share a screen, I will talk you through your trauma (And if that doesn't work out, come remind me that you still want to solve your problems... cuz I will totally forget)

 

[shane_aldrich]

I ended up figuring it out...but if I get stuck again, I may want to take you up on your offer.

Thanks,

Shane