Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I'm having a little trouble figure out what is the best way to express my average over the course of 3 years using Measures.
My data structure looks like this (Table 1). As a note, there are other more unique data-elements (not illustrated below) that won't allow for this Table to be summerized/grouped to a higher level.
FullName | Hours Worked | Location 1 | Location 2 | Labor Category | Fiscal Year John Smith| 10 | ABC | ABC | Worker | 2015 John Smith| 5 | XYZ | XYZ | Manger | 2014
John Smith| 7 | XYZ | XYZ | Manger | 2015
John Smith| 3 | ABC | CDA | Worker | 2013
John Smith| 2 | XYZ | XYZ | Manger | 2015
John Smith| 10 | ABC | ABC | Worker | 2015
John Smith| 5 | XYZ | XYZ | Manger | 2014
John Smith| 7 | XYZ | XYZ | Manger | 2015
John Smith| 3 | ABC | CDA | Worker | 2013
John Smith| 2 | XYZ | XYZ | Manger | 2015
My Measure looks like this:
Avg Hrs per Person = CALCULATE(SUM(Table1[Hours Worked])/DISTINCTCOUNT(Table1[FullName] ), Filter(Table1, [FullName]<>"No Matching Name" && [Location 1]=[Location 2] && ([Labor Category] = "Worker"|| [Labor Category]="Manager")))
Because John Smith was an employee for 3 years and his name remained the same the distinct count of John Smith is always 1 not 3. Now the problem is that I want this measure to represent the average of 3 year's of averages for John Smith; aka an average of an avearage.
I believe that the solution here is to use some combination of AVERAGEX, VALUES, and/or SUMMARIZE but the solution is illuding me.
I've tried creating 3 separate measures (one for each Fiscal Year) but I don't see and easy way to average the result of 3 separate measrues. Also, I've been reading a bit on how Averaging Averages is not the best course of action here, kindly advise if there is a better route. Thanks in advance and let me know if you need any more detail.
Solved! Go to Solution.
Assuming your current measure is working as you want, the solution is just
Yearly Average := AVERAGEX( VALUES(Calendar[Year]), [Avg Hrs Per Person])
All the X routines work the same way... "Iterate over elements in the first parameter, evaluate the 2nd expression for each... then aggregate the results together".
Assuming your current measure is working as you want, the solution is just
Yearly Average := AVERAGEX( VALUES(Calendar[Year]), [Avg Hrs Per Person])
All the X routines work the same way... "Iterate over elements in the first parameter, evaluate the 2nd expression for each... then aggregate the results together".
Keep it simple I suppose, I kept trying to identify the year value in the Values funciton. (I'm new to DAX)
Quick question why can't I just put the acutal expression from the [Avg Hrs Per Person] Measure inside of AVERAGEX? Or more simply.... why the need for the "intermediate" measure....?
That should work fine as well, except you will need to wrap it in a CALCULATE( ). Calling a measure has an "implicit calculate".
This call to CALCULATE is a bit fringe in knowledge -- it is to convert the Row Context (created by the X function) to a Filter Context.
So you are saying this should work?
AVERAGEX(VALUES(Table1[Fiscal Year]), CALCULATE(SUM(Table1[Hours Worked])/DISTINCTCOUNT(Table1[FullName]
), Filter(Table1, [FullName]<>"No Matching Name" && [Location 1]=[Location 2] && ([Labor Category] = "Worker"|| [Labor Category]="Manager"))))
Let's just say... that if it doesn't... only @marcorusso has a chance of telling us why ... 🙂
It does not evaluate to the same average as having the Measure in the AVERAGEX function. I'll defer to @marcorusso as well.
If you have:
M := <exp>
X := aggX ( <table>, [M] )
You are doing:
X := aggX ( <table>, CALCULATE ( <exp> ) )
In your case, you forgot the CALCULATE embedding your measure. In this way, context transition will affect also filter arguments (the FILTER inside CALCULATE).
You should use this:
=
AVERAGEX (
VALUES ( Table1[Fiscal Year] ),
CALCULATE (
CALCULATE (
SUM ( Table1[Hours Worked] ) / DISTINCTCOUNT ( Table1[FullName] ),
FILTER (
Table1,
[FullName] <> "No Matching Name"
&& [Location 1] = [Location 2]
&& ( [Labor Category] = "Worker"
|| [Labor Category] = "Manager" )
)
)
)
)
Can anybody help me in calculating avrages of empoyees for a data of 5 years.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |