Calculate Averages per Different Levels

[#1]

I have a source file with these columns:

Person, Process, Sub-process, Item, Hours.

Process has multiple Sub-processes and Sub-process has multiple Items.

I would need to show Average hours a Person spent on each level. (with the drill down capability if possible).

I am using this expression: SUM(Hours)/Count (DISTINCT Process)

but need to be able to change it dynamically ...say if I select Sub-process or Item.

What would be a best way of doing this. (Chart or a table) and how?

[vishsaggi]

Pivot table should help you in this regards? As it will automatically drill down based on your dimensions. The table will reflect based on your selections? If this is not what you are looking let us know your output.

[#3]

What is the expression I should use for pivot?

[vishsaggi]

Dim:

Process

SubProcess

Item

Expr:

SUM(Hours)/Count (DISTINCT Process)

[#5]

How is this expression going to show Averages per SubProcess and per Item?

Also Person should be in Dimensions as well, right?

[vishsaggi]

Can you share a sample data with expected output ?

[#7]

ok. Attached is the sample data.

Desired output should be averages on all levels (Process, Sub-process and Item) per person.

[Anonymous]

Sergio,

I am having a similar problem as you are having. I will follow this thread to see if a solution is presented. In the meantime I have two charts from my own example that can display how you can show the data. The circular chart is meant to display average sales by week compared to actual sales by week. (This helps the users clearly see outlier points which can lead to deeper sales discussions). The chart on the right is a line chart which should also display actual sales vs. average sales. (The average should always be a straight line of which actual sales can be plotted against so that it can be determined which weeks should be of concern if they are not within a particular threshold compared to average weekly sales.

The formula I use for actual sales is: =sales

The formula I use for average sales is: =Sum(TOTAL Sales)/Count(Distinct Week)

My issue is I can not get my average sales to calculate correctly and be dynamic according to the number of weeks selected.

Thanks,

Brandon

[vishsaggi]

I am not sure if this is what you want? Let us know? See attached.

[#10]

Thank you for your solution Vishwarath.

However, this is not what I need. E.g.

If I select Staff 2. He worked a total of 209 hrs.

That was 3 processes so average would be 209/3

That was 2 sub-processes etc. - so average of that as well. etc.