Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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, @Anonymous 

 

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, @Anonymous 

 

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.

mahoneypat
Employee
Employee

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


Anonymous
Not applicable

@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
Super User

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Tahreem24
Super User
Super User

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, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.