2 Replies Latest reply on May 4, 2018 12:09 AM by Yanbin Cen

    Urgent! A Single Cal for 3 Layers Average

    Yanbin Cen

      Hi All,

       

      Sorry for the duplicated thread, as it is really urgent and it seems the previous thread stopped getting the reply, and I also get some updates of data set from my end.

      Here is the current situation: I have a data set that has following columns: Aspect/Risk/Locations/Score, where each aspect has multiple risks where each risk has multiple locations. Suppose every corresponding risk to an aspect is unique,while different risks may share the same locations. I'd like to know if it would be possible to have a single calculation field that would give me the actual average score of each aspect,risk, and location with help of the filters. I've set filters for  aspect/risk/location, and attached the excel workbook for the reference.

       

      For example, when A1,R1,USA, the calc field should return 1; when A1,R1, and all locations are selected, it should return (1+2+3)/3=2; when A1 and all related risks, and locations are selected, it should return (2+6)/2=4.

       

      In the previous thread, Mavis purposed an idea that is very close to what I want, but it probably didn't solve the problem due to duplicated locations (as I didn't mention it in the thread).

       

      Let me know if you have any thoughts!

      Thanks!

       

      Karen

       

       

       

       

       

      Mavis Liu

        • 1. Re: Urgent! A Single Cal for 3 Layers Average
          Mavis Liu

          Hi Karen,

           

          Due to the timezone differences, it is currently 9pm where I am at so that's why there was a delay in reply to the previous thread.

           

          Please see the attached workbook where I had created another LOD calculation called 'Score Average'.

           

          This calculation is:

          avg(

          {INCLUDE  [Risk] :

          SUM([Score])}

          /

          {INCLUDE [Risk]: SUM(

          { EXCLUDE [Locations] : COUNT([Locations])})})

           

          Please look at the worksheet called 'Score Average' and see if this calculation works for you.

           

          Thanks,

           

          Mavis

          • 2. Re: Urgent! A Single Cal for 3 Layers Average
            Yanbin Cen

            Hi Mavis,

             

            Thanks a lot for your reply! I really appreciate it! I've tried the workbook and it works perfectly, however, still got the average score a little bit off the actual average on the risk level in my real case, but it works for both aspect and location level. I'll double check it. Thanks again for your help!

             

            Karen