Scope of sums larger than scope of List Box field.

[terrusie]

All,

This seemed like a simple question, but after looking at it a while, I'm needing help on the answer.

I have a data set that looks like this:

Job	Name	Parts Completed
Job 1	Name 1	0
Job 1	Name 2	1
Job 1	Name 3	2
Job 2	Name 1	0
Job 2	Name 4	1
Job 3	Name 3	0
Job 3	Name 1	7

And I'm trying to get a list of the employees and the total parts produced by jobs on with which the employee was associated (regardless of that employee's individual contribution).

For example:

Output Desired
Name	Parts Completed
Name 1	11
Name 2	3
Name 3	10
Name 4	1

Notice, Name 1 is associated with 11 even though Name 1 is only directly responsible for 7

Any ideas on how to do this?

[crusader_ · Accepted solution]

Hi,

Try on your dataset, for your sample below formula worked well.

Sum(Aggr(Sum(TOTAL <Job> [Parts Completed]), Name, Job))

Hope this helps!

//Andrei

[terrusie]

Thanks. 

I'll try this.  Can you explain the syntax?  I want to make sure I understand what's going on, so I know how to appropriately reapply it .

[crusader_]

With Aggr function we're creating a virtual table with 2 dimensions: Job and Name, measure is TOTAL sum for each <Job>,

resulting virtual table is like:

Name1, Job1, 3

Name1, Job2, 1

Name1, Job3, 7

.....

Then our external "Chart" dimension Name play a key role to pick up right "name" for "external" SUM() function...

Hope this helps.

//Andrei

[terrusie]

Thanks.  That worked.