Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a what-if parameter table as below.
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.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a what-if parameter table as below.
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.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
Try the Top N feature visual level filter. Refer the below link to get more idea:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |