Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi!
I have a table with Headcount and People Costs by month for three organizations. I am trying to create a measure that calculates the YTD average headcount that works with a slicer for organization. An example of the data is below.
Fiscal Month | Organization | Values | Actual |
January | Shared | Headcount | 5 |
January | Premier | Headcount | 10 |
January | MCS | Headcount | 15 |
February | Shared | Headcount | 6 |
February | Premier | Headcount | 11 |
February | MCS | Headcount | 16 |
January | Shared | People Cost | 500 |
January | Premier | People Cost | 1000 |
January | MCS | People Cost | 1500 |
February | Shared | People Cost | 600 |
February | Premier | People Cost | 1100 |
February | MCS | People Cost | 1600 |
The measure I have written is
Actual YTD HC Avg = TOTALYTD( CALCULATE( AVERAGE( 'AAD CPH'[Actual] ) , 'AAD CPH'[Values] = "Headcount" ) , 'Calendar'[Fiscal Month] , "06/01/2017" )
*Calendar is my date table and is mapped to my fiscal month through a mapping table*
It works fine when I have a single organization selected in my slicer. For example, February YTD HC Avg for Shared is returning 5.5 ( Average of 5 & 6).
However, when I select all organizations in the slicer to show the Total, I get 10.5 instead of the expected 31.5 (Average of 30 & 33). I know it's calculating the average of all 6 numbers to get 10.5, but I don't know how to write the formula to cumulate the numbers within a fiscal month before taking the average.
Well it may not be the most elegant solution, but it seems to work! I removed the Average from the equation and instead divide by a TotalYTD of a DistinctCount. Still interested in more straightforward ways of accomplishing this
Actual YTD HC Avg = ( TOTALYTD( CALCULATE( SUM( 'AAD CPH'[Actual] ) , 'AAD CPH'[Values] = "Headcount" ) , 'Calendar'[Fiscal Month] , "06/01/2017" )) / CALCULATE( TOTALYTD( DISTINCTCOUNT( 'Calendar'[Fiscal Month] ) , 'Calendar'[Fiscal Month] , "06/01/2017" ) )
Hi @stben
You could try the following approach
1. Create a measure called as SumActual = CALCULATE( SUM( 'AAD CPH'[Actual] ) , 'AAD CPH'[Values] = "Headcount" )
2. Create the average measure as AvgActual = AVERAGEX('Calendar', [SumActual])
3. Depending on the slicer for Organization and Date filter you will get the Average value as you wanted.
I am attaching a sample screenshot with the above approach
If this works for you please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
User | Count |
---|---|
81 | |
80 | |
71 | |
70 | |
54 |
User | Count |
---|---|
107 | |
99 | |
88 | |
79 | |
67 |