Daily Average per person per month using # of days worked in the month not days in the month.

[Anonymous]

I am looking to get an average # of units sold per person by the # of days they worked in the month.  I had added a calendar and was able to get a daily average in the month based on number of days in the month but am stuck here.   I added a days worked table but don't know what it needs to join and get the correct results.  Any help would be greatly appreciated - a sample of my detail is below. 

Thanks!! 

[MattAllington · Accepted solution]

Welcome to Power BI. There is a lot to learn.

The units table and days worked tables are data/fact tables.  They are at different levels of granularity (one is day, one is month).  If the data is small, a simple solution would be.

1. create an employee lookup/dim table with all unique employee names

2. create a month level calendar table (Read more here)  https://exceleratorbi.com.au/power-pivot-calendar-tables/  Yours should be month level, not day level.

3. add a new calculated column in the units table to convert the date to the month.

4. join both data tables to both lookup tables on the relevant columns.

5. add employee and month from the lookup tables to a matrix

6. write measures for number of days worked, sum of units sold and [units]/[days]