cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
req7
Helper IV
Helper IV

Chart that shows Top 10 appearance in a table

Hello,
I'm not very familiar with the Rank function and I need some help. My table follows this structure:

Date   Employee
05/05/2020    111
02/06/2020    222
06/18/2020    111
06/29/2020    333
07/05/2020    222
07/11/2020    222


The Employee column shows the identification of each employee who took a particular product from the stock. Each row in the table is equivalent to a product that was picked up by an employee. I want to create a chart that shows only the Top 10 employees who most picked products (employees whose identifications appear more often in the Employee column).

Can someone help me?

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @req7 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

f1.png

 

You may create a what-if parameter table as below.

f2.png

 

Here are the measures.

Count = DISTINCTCOUNT('Table'[Date])

Rank = 
RANKX(
    SUMMARIZE(
        ALL('Table'),
        'Table'[Employee],
        "Count",DISTINCTCOUNT('Table'[Date])
    ),
    [Count]
)

Visual Control = 
IF(
    [Rank]<=[Parameter Value],
    1,0
)

 

Finally you need to put the measure 'Visual Control' in the visual level filter and use the parameter slicer to filter topn which you want to filter out. You may make parameter=10 to test on your side.

f3.png

 

f4.png

 

Best Regards

Allan

 

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

6 REPLIES 6
v-alq-msft
Community Support
Community Support

Hi, @req7 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

f1.png

 

You may create a what-if parameter table as below.

f2.png

 

Here are the measures.

Count = DISTINCTCOUNT('Table'[Date])

Rank = 
RANKX(
    SUMMARIZE(
        ALL('Table'),
        'Table'[Employee],
        "Count",DISTINCTCOUNT('Table'[Date])
    ),
    [Count]
)

Visual Control = 
IF(
    [Rank]<=[Parameter Value],
    1,0
)

 

Finally you need to put the measure 'Visual Control' in the visual level filter and use the parameter slicer to filter topn which you want to filter out. You may make parameter=10 to test on your side.

f3.png

 

f4.png

 

Best Regards

Allan

 

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

mahoneypat
Super User IV
Super User IV

Here is an example of how to do it with TOPN with your example data.  I put N=2 since you only had 3 employees in mock dataset, so change that to 10, and replace with your actual Table[Column] names.  Use this measure in a Table visual with your Employee column.

 

Top Count =
CALCULATE (
    COUNTROWS ( Employees ),
    KEEPFILTERS (
        TOPN (
            2,
            ALL ( Employees[Employee] ),
            CALCULATE ( COUNTROWS ( Employees ) ), DESC
        )
    )
)

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat 

Thank you! Helped me a lot!
Just a small question before closing the topic: this measure works perfectly in a chart, but it doesn't seem to work in a table view. Do you know what could be done to show the same results (top 10 employee identifications that most took products from stock), but in a table view?

It should work in a table if you just have the Employee number field.  However, it will give unexpected results if you have other fields in your table that filter the table with that number field.  Please try this version, which will remove filters on any other fields from the Employees table in determining the Top 2 (switch to 10 again).

 

Top Count 2 =
CALCULATE (
    COUNTROWS ( Employees ),
    KEEPFILTERS (
        TOPN (
            2,
            ALL ( Employees[Employee] ),
            CALCULATE (
                COUNTROWS ( Employees ),
                ALLEXCEPT ( Employees, Employees[Employee] )
            ), DESC
        )
    )
)

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Greg_Deckler
Super User IV
Super User IV

@req7 - Here is a link that helps explain RANKX, lots of examples:

https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452


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

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Tahreem24
Super User II
Super User II

Try the Top N feature visual level filter. Refer the below link to get more idea:

https://www.oreilly.com/library/view/mastering-microsoft-power/9781788297233/378a5195-3e98-4cc1-bcd3...

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

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

Top Solution Authors
Top Kudoed Authors