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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JimSales81
Frequent Visitor

Show count of a category in a dynamic top N model

Hello,

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

 

'Sales' Table
SalespersonUnits SoldRegion
Jim20West
Kelly18West
Roger17East
Pam15East
Sally12West
Tom9East
Mike8West
Joe7East
Steve5West
Lauren3East

 

1.) Created a table called 'TOPN Selection'
Top N
3
5
8
10

 

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!
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1710477973998.png

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1710477973998.png

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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.

Hi,

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

 

Jihwan_Kim_0-1710565707359.png

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors