Difference of a measure and the (static) mean of multiple years

[xuri]

Hello,

I want to calculate the difference between a year's value and the mean value over all years as modulus.

Values:

2013: 300

2014: 170

2015: 250

2016: 320

Mean:

260

What I want to display in a table is:

2013  40

2014  90

2015  10

2016  60

I do not have my values as a column, actually, it is a measure which lists the values per year in a table visualization. The year is a column in a table and the value is a calculated measure connected the the same table.

Another problem I encountered is that the mean changes if I use a slicer and I don't get this to work properly. Does anyone have an idea how to achieve this? 

Thanks in advance
xuri

[Daniil · Accepted solution]

Something along these lines:

Difference =
ABS (
    AVERAGEX (
        ALLSELECTED ( TheTable[Year] ),
        [The Measure]
    )
        - [The Measure]
)

[xuri]

Thank you for the quick response! Both solutions worked perfectly!

[MFelix · Accepted solution]

Hi @xuri,

Taking into account that you need to have a average of the measure you can add this measure:

Difference = [Total values] - AVERAGEX(ALL(Years[Year]),[Total values])

If you want you can make the second part a easure by itself and call it mean and the do Total Values - Mean.

Final result is below:

Regards,

MFelix

[Daniil · Accepted solution]

Something along these lines:

Difference =
ABS (
    AVERAGEX (
        ALLSELECTED ( TheTable[Year] ),
        [The Measure]
    )
        - [The Measure]
)