Forum Discussion
Helper IIITop N for each month
Hi PBI Community, I have been using Top N for a while but yet today, I am stumped. I have a report of our invoices which are issued by a team of many pple (e.g. Member 1, 2, 3, 4, 5 and so forth). I want to chart out the billings by month but only show the TOP 5 (members) and thier billings. When I put the member into filter/visual, PBI took the top 5 for the entire year (Jan to Oct) and plotted out the invoices for the top 5. This is not what I need. I want to know who are the ones who make up the top 5 for Jan, the top 5 for Feb, and so on, and so forth.
E.g.
Jan 2020 - M1, M3, M5, M7, M9, M4
Feb 2020 - M2, M4, M5, M7, M9
Each month is possibly a different group of top 5.
I hope someone can help guide me. Thank you.
Sincerely,
Patrick
Hi, PGAT
According to your description and expected result, you can follow my steps:
- Create these measures:
Rank = RANKX( FILTER(ALLSELECTED('Table'), [Year Month]=MAX([Year Month])), CALCULATE(MAX([Billing])),, DESC)No1 = CALCULATE(MAX([Member]),FILTER('Table',[Rank]=1))No2 = CALCULATE(MAX([Member]),FILTER('Table',[Rank]=2))No3 = CALCULATE(MAX([Member]),FILTER('Table',[Rank]=3))No4 = CALCULATE(MAX([Member]),FILTER('Table',[Rank]=4))No5 = CALCULATE(MAX([Member]),FILTER('Table',[Rank]=5))- Create a Matrix chart, and place columns like this:
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi
Please watch this video that gives a comprehensive solution to your problem
https://www.youtube.com/watch?v=tgL7D3JTa_E&feature=youtu.be
Cheers
David
- amitchandak
Super User
PGAT , You can have measure like this
Rankx(filter(all(Table[Month year], Table[invoices]), [Month year] =max([Month year]) ),[billings],,desc)
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measureshttps://www.youtube.com/watch?v=DZb_6j6WuZ0
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
PGATThank you, Amitchandak. I will try.
- v-robertq-msft
Community Support
Hi, PGAT
According to your description and expected result, you can follow my steps:
- Create these measures:
Rank = RANKX( FILTER(ALLSELECTED('Table'), [Year Month]=MAX([Year Month])), CALCULATE(MAX([Billing])),, DESC)No1 = CALCULATE(MAX([Member]),FILTER('Table',[Rank]=1))No2 = CALCULATE(MAX([Member]),FILTER('Table',[Rank]=2))No3 = CALCULATE(MAX([Member]),FILTER('Table',[Rank]=3))No4 = CALCULATE(MAX([Member]),FILTER('Table',[Rank]=4))No5 = CALCULATE(MAX([Member]),FILTER('Table',[Rank]=5))- Create a Matrix chart, and place columns like this:
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- PGAT
Thank you, v-robertq-msft, i will try.
