TM1 Forum

Good day,

I believe I've read all the consolidated average posts already however I have not found the answer I am looking for.

My situation:
1) I want to determine the average invoice processing By Step. An invoice can have multiple steps, "submit", "code","approve", "final approval".
2) I want to determine the average invoice processing By Department. Each Step is performed by a department, a department can perform more than one step within an invoice.

My data Columns:
Employee (rolls up to department), Invoice, Processing Time, Step, Count (value of 1).

I can determine the average processing time by Step however by department is the problem. When consolidation happens and you try to calculate average based on "Processing Time" / "Count" I end up with skewed results. In the example below where department is "X" count would = 2 at consolidation whereas I need it to be 1. I need to generate a new "Count" based on uniqueness of department.

Example of Data:
Department | Invoice | Step | Processing Time | Count
x | 1 | submit | 1 | 1
x | 1 | code | 3 | 1
y | 1 | approve | 2 | 1
z | 1 | final approval | 6 | 1
x | 2 | submit | 2 | 1
y | 2 | code | 3 | 1
z | 2 | approve | 5 | 1

Please let me know if I need to explain myself better.

Thanks so much in advance.