UREGENT: Calculate average of measure of number of employees at end of period

[AC_71]

Hi,

I am having real trouble trying to calculate an average of values in a table.

The table seems simple enough. It shows the number of sick days each period and the corresponding number of employees employed at the end of each period (See below). This is so I can create an average number of sick days for each period.

Employees At End Of Period = 
VAR selectedDate = MAX(dim_Dates[Calendar_Date])

RETURN 

SUMX('dim_Employee_Basic Info',
VAR employeeStartDate = [Start Date]
VAR employeeEndDate = [Left Date]
RETURN IF(employeeStartDate<= selectedDate && OR(employeeEndDate >= selectedDate, employeeEndDate=BLANK() ),1,0)
) 

My issue is that I do not want to use the number of employees at the end of each period, but the average of the number of employees at the end of each period over the year. ie. Sum all 12 values and divide by 12. The average in the example should be 713.83.

How on earth do I calculated an average of a measure? I have read the internet and been unable to find anything that gets me even close to an answer!

Please help!

Thanks

[AC_71]

Will do. Struggling to recreate my issue with the dummy data! Thank You!!!!!

[AC_71]

Many thanks for this. Unfortunately still not there. The start and left dates come from an 'Employee table'.

The Employees at end of Period is a measure, so each row item is calculated. When I try to enter the average formula it does not appear as an option. The only choices are the original columns of data and not the calculated measures.

Are there different ways of creating measures? I do not understand how it works fore you and not for me.

[mcaraballo]

Could you send me the pbix file that you are using  with dummy data? So that I can see the data model and create the metrics. 

the average that I show in the last picture, that is what you need?

[AC_71]

Dont know how to insert a pbix file.

[v-yaningy-msft]

Hi, AC_71

Here are some ways to help you upload some relevant issue data.

1. You can post data tables (that do not contain sensitive information and cover the question), measures, and expected outputs in the Reply.
2. The other option is to put your files in a dropbox, onedrive or googledrive folder and share the link with the community
3. Please refer to the related links for details:

   How to provide sample data in the Power BI Forum - Microsoft Fabric Community

   Best Regards,
   Yang
   Community Support Team

   If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
   If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

   How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[mcaraballo]

Hi @AC_71, 

If I did not misunderstand, you need to show 671/713.83 for 01-Apr, 676/713.83 for 01-May and so on. I don´t know how you created the Start Date and Left Date so I have hardcoded the values in the Power Query:

 then, I created these measures:

Employees at end of period = calculate(sum('Table'[Employees at end of period])) --> it would be the same that you have as Employees At End Of Period.

AVG Employees at end of period = calculate(AVERAGE('Table'[Employees at end of period]), all('Table'[Date])) --> the use of ALL DAX function is the key for your need. Check how it is shown in the table: 

Then, you just have to divide both metrics:

Expected values = [Employees at end of period] / [AVG Employees at end of period] --> you can use the DIVIDE DAX and assign an alternate result )

 Hope this is what you need.

Please, if it works accept my reply as the solution.

[AC_71]

Sorry, I can't seem to get the table to be a table. As soon as I post, it goes wrong.

[AC_71]

Date	Period	Sum of Sick Days
29/04/2023	01-Apr	319
27/05/2023	02-May	300
01/07/2023	03-Jun	407.5
29/07/2023	04-Jul	379
02/09/2023	05-Aug	433
30/09/2023	06-Sep	443
28/10/2023	07-Oct	430
02/12/2023	08-Nov	414.5
30/12/2023	09-Dec	415
27/01/2024	10-Jan	394
02/03/2024	11-Feb	502.5
30/03/2024	12-Mar	545

Employee Number	Start Date	Left Date
1	25/11/1984	13/09/2023
2	28/01/1985	17/02/2023
3	17/02/1985	27/03/2023
4	05/05/1985	06/07/2011
5	15/07/1985	01/01/9999
6	19/08/1985	01/01/9999
7	04/08/1986	26/11/2024
8	14/09/1986	02/02/2024
9	26/10/1986	01/01/9999
10	01/11/1986	27/05/2023
11	02/02/1987	22/05/2023
12	27/04/1987	01/01/9999
13	15/06/1987	01/01/9999
14	18/06/1987	30/07/2024
15	26/07/1987	04/02/2023
16	13/10/1987	01/01/9999
17	01/11/2023	01/01/9999
18	18/01/1988	01/01/9999
19	29/02/1988	01/01/9999
20	17/03/1988	28/01/2023
21	02/05/1988	01/01/9999
22	12/06/1988	01/01/9999
23	19/06/1988	01/01/9999
24	27/06/1988	01/01/9999
25	23/07/1988	27/06/2023

Using the measure posted previously, you can calculate the emaployees at the end of each period. I am looking to get the average of this measure. Desired output should look something like this:

Many Thanks!

[khaledHabib90]

Hello,

try this

= Averagex(values(all(period)),[Employees At End Of Period])

if it doesnt work I recommend that you attach a sample pbix

if it works please accept my reply as the solution

[AC_71]

Hi, I get 'The VALUES function expects a column reference expresion or a table reference expression for argument '1'.

[AC_71]

Thank you for the response.

This is close, but not quite right. I need the average employees at the end of the month to appear monthly for each period. It should be the same value for each month. In effect, it is summing up the displayed values and dividing them by 12.

[v-yaningy-msft]

Hi, @AC_71 

May I ask if this is the expected output you are looking for? Based on your second description, I have created a measure to achieve the effect you are looking for. Following picture shows the effect of the display.

Measure:

Average Employees At End Of Period = AVERAGEX(SUMMARIZE(ALLSELECTED('Calendar'),'Calendar'[Month],"@totals",[Employees At End Of Period]),[@totals])

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[v-yaningy-msft]

Hi, @AC_71 

May I ask if this is the expected output you are looking for? Based on your description, I have created a measure to achieve the effect you are looking for. Following picture shows the effect of the display.

Measure:

Average Employees at end of period = AVERAGEX(SUMMARIZE('Calendar','Calendar'[Month],"@totals",[Employees At End Of Period]),[@totals])

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...