2 Replies Latest reply on Aug 12, 2016 1:34 AM by Charchit Joshi

# Peer average calculation.

I want to create a Calculated measure to calculate the peer average. The worksheet has a filter to select companies for which I want to do such calculation.

Problem Set: To calculate the peer average value ( Average of values for all companies except for the one selected in filter)

Action So Far: I created a calculated measure using '{ exclude [Company]: AVG([Value])}

However as against the expected result the calculated measure returns the values for the company selected in the Filter. I was hoping if I can calculated the average of values of the companies that are not selected in the field and plot it alongside the values of selected companies.

• ###### 1. Re: Peer average calculation.

Hi Charchit,

I think using LOD with Exclude wouldn't accomplish your goals: LODs are designed to operate on entire dimensions, and not on individual values that are filtered in our out.

I propose a different approach: self-join on Company. Then you can define a calculated field that should serve your purpose as follows:

SUM(IF([Company (Sheet11)]==[Company]) THEN 0 ELSE [Value (Sheet11)] END)/

SUM(IF([Company (Sheet11)]==[Company]) THEN 0 ELSE 1 END)

Attaching a workbook that shows this approach. I named the calculated field I defined above PeerAvgByCompany. I dragged Date to Columns, the new calculated field to Rows, and Company to Color.

Hope this helps. Let me know if you have other questions.

• ###### 2. Re: Peer average calculation.

ThanksStoyko Kostov,

Apologies for the delayed reply to this. Thank you for looking at the file. I will try to implement the solution in my project. If in case I come across any issue, will need your help again.

Thank you