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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous ,

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-ranking/m-p/322064 

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/
TomMartens
Super User
Super User

Hey @Anonymous ,

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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