Finding Max value in a group

[Jorgast]

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

[v-yuezhe-msft]

@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

[Jorgast]

My 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

[v-yuezhe-msft · Accepted solution]

@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

[Jorgast]

@v-yuezhe-msft

This is better than what i had i was thinking. Thanks

[Jorgast]

@v-yuezhe-msft 

Would the code still work when the data is sorted in a different manner?

When i try using the First Non blank, it seems to be looking at the first non blank record in the data set, so everything is being compared to that.

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])))

Customer NameSale Agent NameSale AmountAdjusted Sale AmountDate of saleComment

Falcon	David	375	375	11/1/2017	Originating Sales Rep is different from final
Hawkeye	Carl	350	350	11/1/2017	Originating Sales Rep is different from final
Hulk	Bob	200	200	11/1/2017	Originating Sales Rep is different from final
IronMan	Mary	275	275	11/1/2017	Originating Sales Rep is different from final
Hulk	Bob	200	225	11/5/2017
Hulk	John	225	300	11/5/2017	Originating Sales Rep is different from final
IronMan	Mary	275	300	11/6/2017	Originating Sales Rep is different from final
Hawkeye	Matt	350	325	11/10/2017	Originating Sales Rep is different from final
Falcon	Matt	375	450	11/11/2017	Originating Sales Rep is different from final
IronMan	Matt	300	400	11/12/2017	Originating Sales Rep is different from final