topic Re: Show count of a category in a dynamic top N model in DAX Commands and Tips

Show count of a category in a dynamic top N model

JimSales81 — Fri, 15 Mar 2024 03:14:17 GMT

Hello,

I successfully created a dynamic Top N model in Power BI using the following data

'Sales' Table

1.) Created a table called 'TOPN Selection'

Top N

2.) Created a measure called Selected N=

Selected N = SELECTEDVALUE('TOPN Selection'[Top N])

3.) Created a Units Rank Calculated Column

Unit Rank = rankx(Sales, Sales[Units Sold], , DEsc)

4.) Created a slicer Control

Slicer Control = var SelectedTop=Selectedvalue('TopN Selection'[Top N])

Return

if(SELECTEDVALUE(Sales[Unit Rank])<=SelectedTop, 1, 0)

5.) Added the Slicer control as a filter and selected is 1

It works great. But what i want to do is add a card visual that shows the count of West Salespeople dependent on the Top N selection

So when Top 3 is Selected my Card visual will say 2 (Jim & Kelly)

When Top 5 is Selected it will say 3 (Jim, Kelly, & Sally)

When Top 8 is Selected it will say 4 (Jim, Kelly, Sally, & Mike)

When Top 10 is Selected it will say 5 (Jim, Kelly, Sally, Mike, & Steve)

I can't figure out the dax. Please advise.

Thank you!

Jihwan_Kim — Fri, 15 Mar 2024 04:46:46 GMT

Hi,

Please check the below picture and the attached pbix file.

JimSales81 — Fri, 15 Mar 2024 20:04:18 GMT

Jihwan_Kim,

THis is perfect. Similarly how would i write a measure to get the Total Units sold by Salespeople in the west based on the top N selection.

So if 3 was selected the total units sold in the west would be 38 but if 5 was selected it would change to 50, etc.

Jihwan_Kim — Sat, 16 Mar 2024 05:08:45 GMT

Hi,

thank you for your message, and please check the attached pbix file.