8 Replies Latest reply on May 2, 2018 12:46 PM by Yanbin Cen

Urgent! Single Calculation Field for Three Layers Average

Hi All,

I have an urgent issue that need your talent!

I have a data set that has following columns: Aspect/Risk/Themes/Theme_Score where each aspect has multiple risks while each risk has multiple themes. Suppose there's no theme overlapped among any risk, and the same of risk over aspect.  I'd like to know if it would be possible to have a single calculation field that would give me the actual average of each aspect, risk, and theme with help of the filters. I've set filters for aspect,risk,theme. Take the following table as an example, I'd like to have a single calc field so that when I choose A2,R3,T4, it would return 4; when A2,R3,and all relevant theme, it would return (4+5+6)/3=5; when A2 and all relevant risks, and themes, it would return [(4+5+6)/3+7/1]/2=6.

Thanks a lot!

Karen

Aspect
Risk
Theme
Score
A1R1T11
A1R2T22
A1R2T33
A2R3T44
A2R3T55
A2R3T66
A2R4T77
• 1. Re: Urgent! Single Calculation Field for Three Layers Average

Hi Yanbin,

Please could you try this LOD and see if it works? -

AVG(({EXCLUDE  [Theme] : SUM([Score])})

/

(

{ EXCLUDE  [Theme]:

COUNT([Theme])}))

I've also attached the workbook with the calculation and relevant filters. Please try and let me know if this works. If this answers your question, please mark this as answered,

Thanks,

Mavis

• 2. Re: Urgent! Single Calculation Field for Three Layers Average

Hi Mavis,

Thanks a lot for your reply! I've  checked out the workbook, but it seems the method  only works on the aspect level. As I mentioned, I also need it to work on risk and also theme level. Say, if I choose A2, AND r3, it would return the average  of R3, and if I specify the theme, it would also return the score of the theme.

Let me know if you have any further thoughts!

Thanks!

• 3. Re: Urgent! Single Calculation Field for Three Layers Average

Hi Yanbin,

Using your example, in my workbook if I select A2 and R3, it returns the value of 5:

If I then choose A2 and R3 and T4 it returns a value of 4:

Is this incorrect?

Thanks,

Mavis

• 4. Re: Urgent! Single Calculation Field for Three Layers Average

Hi Mavis,

Thanks! I apologize for my carelessness. It does work! I'll try it on my own workbook and see if it still works!

• 5. Re: Urgent! Single Calculation Field for Three Layers Average

Hi Mavis,

I have actually have one more question: what if I have one more layer? Say ''Location" is the layer at the bottom (each theme has multiple layer, and the score represents the theme score at a specific location) and there may have overlapping locations over all risks.How should I modify the LOD? I wanna get average of all the layers.

Thanks!

• 6. Re: Urgent! Single Calculation Field for Three Layers Average

Hi Yanbin,

Please could you show me an example of this new dataset with the location? I'd like to see what you mean by overlapping location over all risks and how you'd expect this to be calculated. Some example scenarios and example scores like in your original question would be great.

Thanks,

Mavis

• 7. Re: Urgent! Single Calculation Field for Three Layers Average

Hi Mavis,

Thanks for the reply! I've attached an Excel workbook to the original post for your reference. So Basically, I need a single cacl that would return all three AVg columns when appropriate filters are used. For example, to get A1+R1+T1, I take average of all scores related to T1 which is (1+2)/2=1.5, to get A1+R1, (T1+T2)/2=(1.5+3)=2.25, to get A1, (R1+R2)/2=(2.25+6)/2=4.125.

Let me know if you have any thoughts!

• 8. Re: Urgent! Single Calculation Field for Three Layers Average

Hi Mavis,

I just updated the workbook I uploaded, as I found the mistakes of my calculation in the workbook, but still the same logic as in the previous reply, but without themes column. I actually made another thread to make my problem more clarified, as themes have been removed from the data set. If you still get confused, you can think my real problem very similar to my original post(aspect/risk/theme/score columns), except that different risks may share the same theme in my real case, while in the previous case, there was no risk that would have any common theme.

Thanks a lot!