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

    Peer average calculation.

    Charchit Joshi

      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.
          Stoyko Kostov

          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.
            Charchit Joshi

            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