March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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
Will do. Struggling to recreate my issue with the dummy data! Thank You!!!!!
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.
Here are some ways to help you upload some relevant issue data.
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
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:
Then, you just have to divide both metrics:
Sorry, I can't seem to get the table to be a table. As soon as I post, it goes wrong.
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!
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'.
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.
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
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
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...
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
27 | |
27 | |
21 | |
19 |