cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jorgast Resolver I
Resolver I

Finding 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 NameSale Agent IDCustomer NameCityStateZipSale AmountAdjusted Sale AmountDate of sale
Bob1000Hulk ChicagoIL1234520020011/1/2017
John2000ThorAtlantaGA2345625025011/1/2017
Mary3000IronManNew YorkNY3456727527511/1/2017
Luke4000SpiderManQueensNY4567830030011/1/2017
Allen5000CaptainBrooklynNY5678932532511/1/2017
Carl6000HawkeyeSanFranCA6789035035011/1/2017
David7000FalconSeattleWA7890137537511/1/2017
         
Bob1000Hulk ChicagoIL1234520022511/5/2017
John2000ThorAtlantaGA2345625027511/6/2017
Mary3000IronManNew YorkNY3456727530011/6/2017
Luke4000SpiderManQueensNY4567830025011/8/2017
Allen5000CaptainBrooklynNY5678932550011/9/2017
Matt8000HawkeyeSanFranCA6789035032511/10/2017
Matt8000FalconSeattleWA7890137545011/11/2017
Matt8000IronManNew YorkNY3456730040011/12/2017
         
Bob1000SupermanHoustonTX1234520022511/5/2017
John2000Hulk ChicagoIL1234522530011/5/2017
1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Finding Max value in a group

@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.
1.JPG



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Moderator v-yuezhe-msft
Moderator

Re: Finding Max value in a group

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Jorgast Resolver I
Resolver I

Re: Finding Max value in a group

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 NameSale Agent NameSale AmountAdjusted Sale AmountDate of saleComment
FalconDavid37537511/1/2017Originating Sales Rep is different from final
FalconMatt37545011/11/2017Originating Sales Rep is different from final
HawkeyeCarl35035011/1/2017Originating Sales Rep is different from final
HawkeyeMatt35032511/10/2017Originating Sales Rep is different from final
Hulk Bob20020011/1/2017Originating Sales Rep is different from final
Hulk Bob20022511/5/2017Originating Sales Rep is different from final
Hulk John22530011/5/2017Originating Sales Rep is different from final
IronManMary27527511/1/2017Originating Sales Rep is different from final
IronManMary27530011/6/2017Originating Sales Rep is different from final
IronManMatt30040011/12/2017Originating Sales Rep is different from final
Moderator v-yuezhe-msft
Moderator

Re: Finding Max value in a group

@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.
1.JPG



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Jorgast Resolver I
Resolver I

Re: Finding Max value in a group

@v-yuezhe-msft

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

Jorgast Resolver I
Resolver I

Re: Finding Max value in a group

@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

FalconDavid37537511/1/2017Originating Sales Rep is different from final
HawkeyeCarl35035011/1/2017Originating Sales Rep is different from final
Hulk Bob20020011/1/2017Originating Sales Rep is different from final
IronManMary27527511/1/2017Originating Sales Rep is different from final
Hulk Bob20022511/5/2017 
Hulk John22530011/5/2017Originating Sales Rep is different from final
IronManMary27530011/6/2017Originating Sales Rep is different from final
HawkeyeMatt35032511/10/2017Originating Sales Rep is different from final
FalconMatt37545011/11/2017Originating Sales Rep is different from final
IronManMatt30040011/12/2017Originating Sales Rep is different from final

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors