8 Replies Latest reply on Oct 17, 2018 7:04 AM by meenu choudhary

# Need help with Top Sold Item for each Agent

Hello,

Currently I am working on a dashboard which tracks the performance of Sales agents.
So for each agent I want to plot the total items sold, total sales and top selling item.
Top sold item is on quantity, but if there are two item with same count then consider the item with highest revenue; if there is still a tie, then show all.

I am trying to figure this out with LOD expression but not sure how this will work. Please advice.

Regards,
Aniket

• ###### 1. Re: Need help with Top Sold Item for each Agent

Can you share a sample workbook?

• ###### 2. Re: Need help with Top Sold Item for each Agent

HI Aniket,

Without looking into data, its difficult to suggest any logic.

You can try LOD function to achieve this

1. total items sold

{fixed [Agent] : COUNT([ITEM])}

2. total sales :

{fixed [Agent] : SUM([Sales])}

for [top selling item]logic, it would help if you can attach some mock-up data/workbook.

• ###### 3. Re: Need help with Top Sold Item for each Agent

Sample data :

 Users Items Price User1 Item1 100 User2 Item2 200 User3 Item3 300 User1 Item1 100 User2 Item5 200 User3 Item6 300 User1 Item1 100 User2 Item2 200 User3 Item6 300 User1 Item1 100 User2 Item2 200 User3 Item6 300 User1 Item1 100 User2 Item2 200 User3 Item6 300
• ###### 4. Re: Need help with Top Sold Item for each Agent

1. total items sold

{fixed [Users] : COUNT([Items])}

2. total sales

{fixed [Users] : SUM([Price])}

3. top selling item

{FIXED [Items]:sum([Number of Records])}

For [top selling item]

DO you want to display this by users??

With the attached data could you please explain what value are you expecting for 3rd calculation??

• ###### 5. Re: Need help with Top Sold Item for each Agent

based the data, expected answer should be like:

usertop selling item
User1Item1
User2Item2
User3Item6

This is because of the fact that User1 sold Item1 5 times becomes his top selling item, User2 sold item2 4 times and item5 once, thus item2 becomes his top selling item and so on

• ###### 6. Re: Need help with Top Sold Item for each Agent

Hi,

You can try below logic:

1. Units sold :

{FIXED [Items]:SUM([Number of Records])}

2. top selling item

{ FIXED [Users] :MAX(if {FIXED [Users]:MAX([Units sold])}= [Units sold] then [Items] END)}

• ###### 7. Re: Need help with Top Sold Item for each Agent

Great! This worked perfectly for me.
Could you also explain the top selling logic please?

{ FIXED [Users] :MAX(if {FIXED [Users]:MAX([Units sold])}= [Units sold] then [Items] END)}

Thanks!

• ###### 8. Re: Need help with Top Sold Item for each Agent

I will split the logic:

Actual Logic:

{ FIXED [Users] :MAX(if {FIXED [Users]:MAX([Units sold])}= [Units sold] then [Items] END)}

logic 1 =  {FIXED [Users]:MAX([Units sold])}

To get the maximum unit sold for each user

logic 2 = if [logic 1]= [Units sold] then [Items] END

When maximum unit is = Unit sold then item else null.

logic 3 =  { FIXED [Users] :MAX([logic 2])}

Maximum item for each user, this maximum is to eliminate the "null" values that will come from logic 2, where logic 1<> unit sold.