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.
Hi all, I have created a matrix as follows, which displays a list of the top 25 employees by hrs. I've used the top n Filter to filter the data by hrs. What I would like to do is create a card from this list and display only the top value as per below it should be 159,2 on my dashboard. I've tried many different ways using the max function but its giving me strange results. The hours are summed per month by the matrix. Any help would be appreciated. Thanks
Company X | Department A | Employee number | July | August | Total |
COMP A | DEPT A | 1234 | 1280 | 31,2 | 159,2 |
5678 | 79,2 | 51,4 | 130,7 | ||
9101 | 72,8 | 51,5 | 124,3 |
Solved! Go to Solution.
Hi,
This is then just hit and trial. Try this measure
=MAXX(VALUES(Data[Employee number]),[SumHrs])
Hope this helps.
Hi,
Share the link from where i can download your PBI file.
Apologies but I am not permitted to share the information
Hi,
This is then just hit and trial. Try this measure
=MAXX(VALUES(Data[Employee number]),[SumHrs])
Hope this helps.
So simple thanks! This worked for me!
You are welcome.
Can you scramble data in the PBI file and then share the download link.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
The measure I use for the values in the columns is
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@Anonymous , Information is not sufficient. But you can use Rank and TOPN or Both one of them together
example
City and ID Rank = RANKX(all(Geography[City],Geography[City Id]),[Sales]) // Filter 25. You can use only one column in all, I am giving an example of 2 because your matrix shows 2 column s
Top 25 City Rank = CALCULATE([Sales],TOPN(25,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id])) // 25 record are filtered
Different Example //Top2 is table Name
Top Rank = rankx(ALL(Top2),[PO],,DESC,Dense) + RAND()/1000 // Rank is created on measure PO + rand to aviod overlap
Top 25 Shop Rank = CALCULATE([PO],TOPN(25,all(Top2),[Top2 Rank],ASC),VALUES(Top2)) // Rank has been filtered , grouped on table
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |