Forum Discussion
Resolver IIFinding Max value in a group
Hi Everyone,
I am trying to run sales data and determine who got credit for the sale based on the date. I want to put this in a table to show where the originating agent and the final sales agent are not the same person. The issue i am running into that i only have a date stamp and not date/time. How can i do a comparison of the data if i group by the customer name? I would also like to show the Adjusted Sale Amount for each agent in the comparison. I have tried to create a unique identifier by concatinating the columns but that didnt work. Here is some dummy data to give you an idea of the data i am using. When the Sale amount = Adjusted amount that means this was a new customer and there were no adjustments to be made.
| Sale Agent Name | Sale Agent ID | Customer Name | City | State | Zip | Sale Amount | Adjusted Sale Amount | Date of sale |
| Bob | 1000 | Hulk | Chicago | IL | 12345 | 200 | 200 | 11/1/2017 |
| John | 2000 | Thor | Atlanta | GA | 23456 | 250 | 250 | 11/1/2017 |
| Mary | 3000 | IronMan | New York | NY | 34567 | 275 | 275 | 11/1/2017 |
| Luke | 4000 | SpiderMan | Queens | NY | 45678 | 300 | 300 | 11/1/2017 |
| Allen | 5000 | Captain | Brooklyn | NY | 56789 | 325 | 325 | 11/1/2017 |
| Carl | 6000 | Hawkeye | SanFran | CA | 67890 | 350 | 350 | 11/1/2017 |
| David | 7000 | Falcon | Seattle | WA | 78901 | 375 | 375 | 11/1/2017 |
| Bob | 1000 | Hulk | Chicago | IL | 12345 | 200 | 225 | 11/5/2017 |
| John | 2000 | Thor | Atlanta | GA | 23456 | 250 | 275 | 11/6/2017 |
| Mary | 3000 | IronMan | New York | NY | 34567 | 275 | 300 | 11/6/2017 |
| Luke | 4000 | SpiderMan | Queens | NY | 45678 | 300 | 250 | 11/8/2017 |
| Allen | 5000 | Captain | Brooklyn | NY | 56789 | 325 | 500 | 11/9/2017 |
| Matt | 8000 | Hawkeye | SanFran | CA | 67890 | 350 | 325 | 11/10/2017 |
| Matt | 8000 | Falcon | Seattle | WA | 78901 | 375 | 450 | 11/11/2017 |
| Matt | 8000 | IronMan | New York | NY | 34567 | 300 | 400 | 11/12/2017 |
| Bob | 1000 | Superman | Houston | TX | 12345 | 200 | 225 | 11/5/2017 |
| John | 2000 | Hulk | Chicago | IL | 12345 | 225 | 300 | 11/5/2017 |
Jorgast，
Create the following columns in your table.Original agent = CALCULATE(FIRSTNONBLANK(Table[Sale Agent Name],""), FILTER(Table, Table[Customer Name]=EARLIER(Table[Customer Name]) && Table[Date of sale]<EARLIER(Table[Date of sale])))
checkcol = IF(Table[Original agent]<>BLANK() && Table[Sale Agent Name]<>Table[Original agent],1,0)
Then set the value of checkcol to 1 in visual level filters.
Regards,
Lydia
5 Replies
Jorgast,
What is the result do you want to get based on the above sample data? Could you please post expected result in table format?
Regards,
Lydia
JorgastMy ultimate goal is to be able to see those customers where the orginating sales agent and final sales agent are different. I would like to filter out those customers where the 1st sales agent and the last sales agent are the same.
Customer Name Sale Agent Name Sale Amount Adjusted Sale Amount Date of sale Comment Falcon David 375 375 11/1/2017 Originating Sales Rep is different from final Falcon Matt 375 450 11/11/2017 Originating Sales Rep is different from final Hawkeye Carl 350 350 11/1/2017 Originating Sales Rep is different from final Hawkeye Matt 350 325 11/10/2017 Originating Sales Rep is different from final Hulk Bob 200 200 11/1/2017 Originating Sales Rep is different from final Hulk Bob 200 225 11/5/2017 Originating Sales Rep is different from final Hulk John 225 300 11/5/2017 Originating Sales Rep is different from final IronMan Mary 275 275 11/1/2017 Originating Sales Rep is different from final IronMan Mary 275 300 11/6/2017 Originating Sales Rep is different from final IronMan Matt 300 400 11/12/2017 Originating Sales Rep is different from final Jorgast，
Create the following columns in your table.Original agent = CALCULATE(FIRSTNONBLANK(Table[Sale Agent Name],""), FILTER(Table, Table[Customer Name]=EARLIER(Table[Customer Name]) && Table[Date of sale]<EARLIER(Table[Date of sale])))
checkcol = IF(Table[Original agent]<>BLANK() && Table[Sale Agent Name]<>Table[Original agent],1,0)
Then set the value of checkcol to 1 in visual level filters.
Regards,
Lydia