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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
AC_71
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.

 

AC_71_1-1701253493725.png

 

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

14 REPLIES 14
AC_71
Frequent Visitor

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

AC_71
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?

Dont know how to insert a pbix file.

 

 

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
Advocate III
Advocate III

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:

mcaraballo_0-1701955082737.png

 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: 
mcaraballo_1-1701955249497.png

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 )
mcaraballo_2-1701955308054.png

 

 Hope this is what you need.
Please, if it works accept my reply as the solution.




AC_71
Frequent Visitor

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

AC_71
Frequent Visitor

DatePeriodSum of Sick Days
29/04/202301-Apr319
27/05/202302-May300
01/07/202303-Jun407.5
29/07/202304-Jul379
02/09/202305-Aug433
30/09/202306-Sep443
28/10/202307-Oct430
02/12/202308-Nov414.5
30/12/202309-Dec415
27/01/202410-Jan394
02/03/202411-Feb502.5
30/03/202412-Mar545

 

Employee NumberStart DateLeft Date
125/11/198413/09/2023
228/01/198517/02/2023
317/02/198527/03/2023
405/05/198506/07/2011
515/07/198501/01/9999
619/08/198501/01/9999
704/08/198626/11/2024
814/09/198602/02/2024
926/10/198601/01/9999
1001/11/198627/05/2023
1102/02/198722/05/2023
1227/04/198701/01/9999
1315/06/198701/01/9999
1418/06/198730/07/2024
1526/07/198704/02/2023
1613/10/198701/01/9999
1701/11/202301/01/9999
1818/01/198801/01/9999
1929/02/198801/01/9999
2017/03/198828/01/2023
2102/05/198801/01/9999
2212/06/198801/01/9999
2319/06/198801/01/9999
2427/06/198801/01/9999
2523/07/198827/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:

 

AC_71_0-1701942906128.png

 

Many Thanks!

 

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

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

 

 

AC_71
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.

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.

vyaningymsft_0-1701835641710.png

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
Community Support
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.

vyaningymsft_0-1701764441971.png

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
Super User
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.

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.