Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi Mark,
I think you should use the "Top" function instead of the Rank().
=Top(Sum(sales),1)
Hope this helps.
Kindly,