Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Is the best/easiest/quickest way to use the same dimension twice is to create the same table again?
Stefan,
79375 is the total for everybody together.
I would like totals by individual.
i hope i'm not putting you into too much bother
thanks
Are you sure?
this is what I get for the 5 days for CustomerID 2006:
Salesman ID | Day | sum([List Price]) |
---|---|---|
79375 | ||
2006 | 1 | 4599 |
2006 | 4 | 4599 |
2006 | 7 | 1299 |
2006 | 9 | 41391 |
2006 | 11 | 27487 |
sorry, this is probably doing your head in
All I want to show is the total of salesman sales against other salesman for the number of days they have worked together.
I dont need to show the days which they have worked together, just the total
So in conclusion I should get a table like below. (figures are not accurate)
SalesmanA | SalesmanB | Salesman A sales compared to SalesmanB |
Salesman2005 | Salesman2005 | 65.00 |
Salesman2005 | Salesman2007 | 4500.00 |
Salesman2005 | Salesman2008 | 187.00 |
Salesman2006 | Salesman2006 | 415.00 |
Salesman2006 | Salesman2007 | 450.00 |
Salesman2006 | Salesman2008 | 441.00 |
Salesman2006 | Salesman2009 | 400.00 |
Salesman2007 | Salesman2005 | 65.00 |
Salesman2007 | Salesman2006 | 400.00 |
Salesman2007 | Salesman2007 | 5197.00 |
Salesman2007 | Salesman2008 | 906.00 |
Salesman2007 | Salesman2009 | 652.00 |
Salesman2008 | Salesman2005 | 65.00 |
Salesman2008 | Salesman2006 | 415.00 |
Salesman2008 | Salesman2007 | 5197.00 |
Salesman2008 | Salesman2008 | 8042.50 |
Salesman2008 | Salesman2009 | 652.00 |
Salesman2009 | Salesman2006 | 400.00 |
Salesman2009 | Salesman2007 | 697.00 |
Salesman2009 | Salesman2008 | 719.00 |
Salesman2009 | Salesman2009 | 652.00 |
sorry, this is probably doing your head in
All I want to show is the total of salesman sales against other salesman for the number of days they have worked together.
I dont need to show the days which they have worked together, just the total
I think I understand. I just wanted to ask you to recheck the total sum under question. For the common days that Salesman2005 and Salesman2006 have worked together, I get days 1,4,7,9,11. The above table is only to demonstrate that Salesman 2006 alone has a total sales for those days of 79375.
You said this is the value for all Salesman, and I was not able to confirm that.
Stefan,
"I think I understand. I just wanted to ask you to recheck the total sum under question. For the common days that Salesman2005 and Salesman2006 have worked together, I get days 1,4,7,9,11. The above table is only to demonstrate that Salesman 2006 alone has a total sales for those days of 79375."
the above is absolutely correct.
so Salesman2006 will have 79375 against Salesman2005.
Salesman2005 will also have his 60181 in same table (table format above)
Ok, but these are the numbers that my above posted expression
=sum( aggr(if(sum([List Price]), sum(total<[Salesman ID],Day> [List Price])), [Salesman ID1],[Salesman ID],Day))
is giving, aren't they?
Please have a look at attached sample.
Regards,
Stefan
Stefan,
I have QV personal edition so I cannot opeb the file.
=sum( aggr(if(sum([List Price]), sum(total<[Salesman ID],Day> [List Price])), [Salesman ID1],[Salesman ID],Day))
This does the tirck. Thank you very much.
But now I cant get the total of Rev in table Rev to display next to A-B in the attached sheet.
That was a bit tricky, please try
= num(sum(total<[Salesman ID]> aggr( if(sum({1}[List Price]), sum(total<Day> {1}[List Price])), [Salesman ID],Day,[Salesman ID])),'##0')
to get the Rev column in your table with both Salesman ID and Salesman ID1.
Hope this correct,
Stefan
edit:
a
= num(sum(total<[Salesman ID]> aggr( if(sum({1}[List Price]), sum(total<Day> {1}[List Price])), [Salesman ID],Day)),'##0')
should be enough, there was an additional Salesman ID dimension in aggr by mistake.
Exactly what I need Stefan.
thank you very much.