Top1 sales per Item and Sales Person

[Marc_Kaiser]

Hi

I have following Pivot Table but I acutally want to display only one single line per combination SalesPerson / Item:

SalesPerson	Item	Sales
SalesPersonA	Item1	100
	Item2	150
	Item3	80
	Item4	190
SalesPersonB	Item1	300
	Item2	400
	Item3	450
	Item4	300
	Item5	320
	Item6	200

The target table (straight or pivot) shall display one line per SalesPerson and Item. I want to display the item with the maximum sales in the combination SalesPerson / Item:

SalesPerson	Item	Sales
SalesPersonA	Item4	190
SalesPersonB	Item3	450

With rank()=1, I am able to identify the max sales item for each SalesPerson. But how can I limit the table to one single line per SalesPerson?

With if(rank(sum(Sales))=1,sum(Sales)) all the other items still show up.

Any ideas?

Thanks, Marc

[#2]

Dimensions=[SalesPerson], [Item]

Expression=max(total <[SalesPerson]> Sales)

You use total <[Fields]> to ignore certain dimensions. Meaning in this case it says "I want the max sales for each person" so it will ignore the item numbers, but will still show the item if you include it as a dimension.

[#3]

Hi Mark,

I think you should use the "Top" function instead of the Rank().

=Top(Sum(sales),1)

Hope this helps.

Kindly,