Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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!! 

 

 

Capture.JPG

 

 

1 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]



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

1 REPLY 1

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]



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.