cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Continued Contributor
Continued Contributor

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

Something along these lines:

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

View solution in original post

Highlighted
Super User III
Super User III

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

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:

mean.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

3 REPLIES 3
Highlighted
Continued Contributor
Continued Contributor

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

Something along these lines:

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

View solution in original post

Highlighted
Super User III
Super User III

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

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:

mean.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Frequent Visitor

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

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors