cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ja832153
Post Patron
Post Patron

Top 30 Records Logic in Analysis services

I would like to create a measure which pulls the top 30 order numbes based on revnue in analysis services tabular model . This data should be pulled from the main fact table and dimension tables.

How to create TOP30 records logic measure in the analysis services without creating any additional tables/Columns?

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Try this measure

Top 30 orders = calculate([revenue],top(30,all(Orders[Order number]),[revenue]),values(Orders[Order number]))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @ja832153 ,

Test the following measure:

revnue Measure

revnue_m = SUM(Ranking[revnue ])

 

revnue Rank

revnueRank = RANKX(ALL(Ranking),[revnue _m],,DESC,Skip)

 

TotalSalaryTop30Rank

revnueTop30 = CALCULATE([revnue_m],FILTER(Ranking,[revnueRank ]<=30))

 

 

refer:

https://community.powerbi.com/t5/Desktop/How-to-calculate-top-10-highest-in-the-table-and-also-ranki... 

https://community.powerbi.com/t5/Desktop/Top-10-based-on-a-measure/m-p/460956 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

Ashish_Mathur
Super User
Super User

Hi,

Try this measure

Top 30 orders = calculate([revenue],top(30,all(Orders[Order number]),[revenue]),values(Orders[Order number]))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

TomMartens
Super User
Super User

Hey @ja832153 ,

 

I think you are looking for the TOPN function: TOPN – DAX Guide

Then you can check if a certain order number is in the TOP 30, flag this order number and filter for the flag.

 

Hopefully, this gets you started.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.