Absolute Average For Subtotal Row

[BrianVT]

Hello-

I have succesfully built measures to show the % deviation from a mean by using these measures:

Total New Orders = sum('New Orders Details'[New Orders])



Mean New Orders = 

    CALCULATE([Total New Orders],

        ALLSELECTED(FY_Lookup))/

            CALCULATE(count(FY_Lookup[Fiscal Year],

                ALLSELECTED(FY_Lookup))



Naval Deviation From Mean=

    CALCULATE([Total New Orders] - [Mean New Orders],

        'New Orders Details'[UIC Naval / Non-Naval]="Naval")



Naval % Deviation From Mean = 

    [Naval Deviation From Mean] / 

        CALCULATE(Mean New Orders],

            'New Orders Details'[UIC Naval / Non-Naval]="Naval")

So, that gives me these wonderful % deviations from the mean for each Fiscal Year (from the Fiscal Years I have selected) as such:

Fiscal Year	Naval New Orders	Mean New Orders	Naval % Deviation From Mean
2014	$701,891,851	$757,845,473	-7.4%
2015	$677,034,378	$757,845,473	-10.7%
2016	$770,176,614	$757,845,473	1.6%
2017	$757,620,939	$757,845,473	-0.0%
2018	$798,923,710	$757,845,473	5.4%
2019	$841,425,344	$757,845,473	11.0%
Total	$4,547,072,835	$757,845,473	500.0%

As you can see, the subtotal row is not showing the average of the rows above, and I know this is pretty common with DAX measures in general because of row context.  It actually is showing the correct amount based on the given measure and the context of the Total row.  But, let's move past that - Not only do I need to adjust my measure(s) so that the subtotal row calculates based on the whole column, but I want it to be the ABSOLUTE AVERAGE of all of the %s in the column so that I can see the average of how far they deviate from the mean, regardless of being negative or not.  Can anyone help me knock that out all at once?

[MFelix · Accepted solution]

Hi  @BrianVT ,

You need to had a AVERAGEX for the total line will be something similar to this:

Deviation for matrix =
IF (
    HASONEFILTER ( Table[FiscalYear] );
    [Naval % Deviation From Mean];
    AVERAGEX ( ALLSELECTED ( Table[FiscalYear] ); [Naval % Deviation From Mean] )
)

[MFelix · Accepted solution]

Hi  @BrianVT ,

You need to had a AVERAGEX for the total line will be something similar to this:

Deviation for matrix =
IF (
    HASONEFILTER ( Table[FiscalYear] );
    [Naval % Deviation From Mean];
    AVERAGEX ( ALLSELECTED ( Table[FiscalYear] ); [Naval % Deviation From Mean] )
)

[BrianVT · Accepted solution]

Thank you @MFelix .  I added an ABS before the mean calculation to make the total line display an absolute average.  Final function is here:

Deviation v2 =
IF (
    HASONEFILTER ( FY_Lookup[Fiscal Year] ),
    [Naval % Deviation From Mean],
    AVERAGEX ( ALLSELECTED ( FY_Lookup[Fiscal Year] ), ABS[Naval % Deviation From Mean] )
)