Urgent! A Single Cal for 3 Layers AverageYanbin Cen May 2, 2018 12:38 PM
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
- Sample_score.xlsx 9.4 KB
1. Re: Urgent! A Single Cal for 3 Layers AverageMavis Liu May 2, 2018 1:17 PM (in response to Yanbin Cen)
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
- Score.twbx 35.2 KB
2. Re: Urgent! A Single Cal for 3 Layers AverageYanbin Cen May 4, 2018 12:09 AM (in response to 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