cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Ranking and measure and additonal filters

I have (in PowerBI) a table with many transactions done by different users.
I'v made a measure to count the number of transactions. So I can make already a visual with the name of a user and the number of transactions done by that user. This measure is working very well, even when i do some other selections (like a filter for the month in which the transaction is done, ... )

Now I want to do a ranking of the users with the user with the most transactions on top. I would like to show only the five first users, so the 5 users with the greatest number of transactions.
I'v tried already different formules, but sometimes all the users get rank 1 (formula counts only for the transactions for specified user). With another formula the ranking uses the total number of transaction of that user (and doesn't use the other filter 'month of the transaction').

How can I solve this problem?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Ranking and measure and additonal filters

Hi @Daniil,

 

Please try below measures:

Count transaction = COUNT('User-transaction'[TransactionID])

Rank value = RANKX(ALLSELECTED('User-transaction'[User]),[Count transaction],,DESC,Dense)

1.PNG2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
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

4 REPLIES 4
Highlighted
Continued Contributor
Continued Contributor

Re: Ranking and measure and additonal filters

Is there a particular reason why you want to use measures for your task?

 

As an alternative, you could try sorting your visual (to display the user with the most transactions on top) and filtering it with the Top N filter type (to show only the top five users by transactions).

 

See here for a video tutorial: https://www.youtube.com/watch?v=i5MRisaNqbU

Highlighted
Microsoft
Microsoft

Re: Ranking and measure and additonal filters

Hi @Daniil,

 

Please try below measures:

Count transaction = COUNT('User-transaction'[TransactionID])

Rank value = RANKX(ALLSELECTED('User-transaction'[User]),[Count transaction],,DESC,Dense)

1.PNG2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
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

Highlighted
Helper III
Helper III

Re: Ranking and measure and additonal filters

Thanks for your advises!

 

I need to use the measure (count of transaction) because it's not included in a table and has to be calculated depending on other filters.

 

I did already tried sorting the visuals, but my problem was that my ranking formula wasn't correct and so i couldn't use the top N filtertype.

 

Highlighted
Helper III
Helper III

Re: Ranking and measure and additonal filters

Hi @v-yulgu-msft

 

Thanks for your example. This has sent me to the solution.

I had forgotten to use the allselected (users) in the rankx formula!

 

 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors