Count Max Sale by User

[Anonymous]

Hey There Community!

I realize this is probably not a complex calculation but the solution is escaping me. I need to count the rows/records by user_id that contain the max id. So in the image below I would want to return a count of the 3 highlighted rows. I've tried various filter and max combinations on the user_id but can't seem to get it right. 

[JarroVGIT]

Hi @Anonymous ,

Fun question. Please note that in order for us to help you quickly, don't post pictures of tables but rather the tables (or files) them selves. If you post pictures, we need to recreate the data.

For this I created a very simple mock up data set:

The outcome of the above woulde be

A	2
B	1

A = 2 because we have 2 times the id of 2 (row 3 and 4). B=1 because we have one time ID 4 (row 8). This is the logic i draw from your post, that is correct?

This measure makes that happening (when used in a table visual!):

Measure = 
VAR _curUserID = SELECTEDVALUE(Table3[user_id])
VAR _curUserIdMaxID = MAXX(FILTER(Table3, Table3[user_id] = _curUserID), [id])
RETURN
COUNTROWS(FILTER(Table3, Table3[user_id] = _curUserID && Table3[id] = _curUserIdMaxID))

The result is this:

The PBIX might be downloaded here (table3, page2, ignore the rest in the file as it answers other questions :))

https://1drv.ms/u/s!Ancq8HFZYL_aiItDUonqIklYNkn7Nw?e=lKuyLA

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Anonymous]

@JarroVGIT - that was a nice solution. I'd considered a calculated column but for the same reason just couldn't get the syntax right. Thank you!

[Anonymous]

Hi @JarroVGIT thanks for the advice. I don't normally post so it never occured to me to supply a PBIX. I updated the one you sent with a sample from my actual table. 

What I'm actually looking for in the measure results is for 1 to be returned for user_id A, which would be the row with the highest id. 

So below, the measure New Contracts would return a total of 3. The rows for each user with lower id's would be filtered out. 

https://1drv.ms/u/s!Aiu-grSaGsWrbjkRftSCDdvkUfc?e=EYSfpo 

[JarroVGIT · Accepted solution]

Ah ok I didn't understood that you wanted to have more information than just the counts of max ID's per User_id in your visual. In that case, add a calculated column to your table like this:

IsMaxIDforUserID = 
VAR _curID = 'User Group Sales'[id]
VAR _curUserID = 'User Group Sales'[user_id]
RETURN IF(MAXX(FILTER('User Group Sales', 'User Group Sales'[user_id] = _curUserID), [id]) = _curID, TRUE, FALSE) 

Then, add that column to the visual filter and filter for True. The column indicates if the current row contains the maximum id for the current userID. 

Result is this:

Your PBIX updated is here:

https://1drv.ms/u/s!Ancq8HFZYL_aiIw3_VDuajJWrCu52w?e=qNrx0g

Please don't forget to like posts that helped you and mark it as the solution if it answered your question. This will help others to find the answers quikcly as well if searching for similar problems 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂