4 Replies Latest reply on Feb 21, 2019 11:04 AM by Pedro Gallo

# Sum & Avg in Hierarchies

Hi team,

I have a problem calculating a value based on a dynamic time frame (depending on the date/time filter) & hierarchy created.

The idea is to show the result based on the dimension of the hierarchy selected:

Below is the following information.

-Zone

--Group Name

---Operation Name

Variables:

-Date/Time

-Zone

-Group Name

-Operation Name

-Part Per Events (constant)

-Total Events: Good Events / Bad Events / No Events

-Average Time (sec)= Avg(Time)

-Time1 = Sum (Time1)

-Time2 = Sume (Time2)

Formula & conditions:

For each Operation Name: Potential = [Total Events(only good events)*Parts Per Events] / ( { [  Avg(Time) * Total Events(only good events) ] + Time1 + Time2 } / 3600)

For each Group Name: Potential = Sum (Potential's Operation Name)

For each Zone: Potential = Avg (Potential's Group Name)

Below is an example of what is expected :

 Zone Group Name Operation Name Parts Per Events Total Events (Good Events) Average Time (sec) Time1 (sec) Time2 (sec) Potential X A A1 1 19 200.0 3240 0 9.7 X A A2 2 18 354.0 1654 1567 13.5 X A A3 1 12 288.0 478 0 11.0 X A A4 1 8 443.0 1789 2230 3.8 X A A5 2 10 546.0 143 0 12.9 Total A 50.9 X B B1 1 10 443.0 4429 105 4.0 X B B2 1 17 567.0 562 0 6.0 X B B3 3 16 655.0 12 5200 11.0 X B B4 1 15 430.0 5112 0 4.7 X B B5 2 9 641.0 6785 5098 3.7 Total B 29.4 X C C1 2 4 445.0 18050 9 1.5 X C C2 1 18 698.0 2542 7 4.3 X C C3 4 19 445.0 340 5660 18.9 X C C4 1 13 223.0 418 4 14.1 X C C5 1 8 121.0 5252 0 4.6 Total C 43.4 Total X Avg 41.2

Thanks for the support

• ###### 1. Re: Sum & Avg in Hierarchies

Hi, Pedro

Can you provide your sample workbook?

ZZ

• ###### 2. Re: Sum & Avg in Hierarchies

Thank for replying Zhouyi. Just attached the workbook and the data.

• ###### 3. Re: Sum & Avg in Hierarchies

Hi, Pedro

Thank you for the workbook, much easier to look at your question.

Please find attached workbook as my solution and below screenshot is the calculation I used.

Not sure whether you will have multiple zone values or not, and not sure what aggregation you need for overall, so I just level it as it is now, but if you have question, please let me know.

ZZ

1 of 1 people found this helpful
• ###### 4. Re: Sum & Avg in Hierarchies

Thank you Zhouyi!