cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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!

Thanks

14 REPLIES 14
Frequent Visitor

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

Frequent Visitor

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.

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?

Frequent Visitor

Dont know how to insert a pbix file.

Community Support

### Hi, AC_71

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

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!

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.

Frequent Visitor

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

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

Resolver II

Hello,

try this

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

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

Frequent Visitor

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

Frequent Visitor

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.

Community Support

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

Community Support

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

Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors