YTD Average with Slicers

[stben]

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.

[stben]

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" ) )

[CheenuSing]

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