Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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 .
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
Thanks. That worked.