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
PeteSil
Frequent Visitor

Ranking a Measure by a Row Item

Hello,

 

I have a measure I created that is named [Cost to Date], & then I have cost codes aligning with those cost's. Example data is below:

 

Cost Code                    Cost to Date

01-200                          $1,000,000

01-201                               $48,000

01-203                          $1,200,000

01-204                           ($100,088)

01-205                             ($10,000)

 

Now if I want to just show the smallest two values from the cost codes, is there a way to use the ranking system to make this possible?

 

All help will be very much appreciated.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@PeteSil , You can use TOPN and Window

 

 

CALCULATE( Sum(Table[Cost to Date]),

TOPN( 2, ALL(Table[Cost Code]), CALCULATE( Sum(Table[Cost to Date])), ASC),

VALUES( Table[Cost Code] ))

 

 

Learn Power BI: Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ

View solution in original post

3 REPLIES 3
grazitti_sapna
Resolver I
Resolver I

Hey,

You can achieve this task by DAX query using TOPN function.

CALCULATE( Sum(Table[Cost to Date]),

TOPN( 2, ALL(Table[Cost_Code]), CALCULATE( Sum(Table[Cost to Date])), ASC),

VALUES( Table[Cost_Code] ))

I hope this will help.

amitchandak
Super User
Super User

@PeteSil , You can use TOPN and Window

 

 

CALCULATE( Sum(Table[Cost to Date]),

TOPN( 2, ALL(Table[Cost Code]), CALCULATE( Sum(Table[Cost to Date])), ASC),

VALUES( Table[Cost Code] ))

 

 

Learn Power BI: Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ

This worked. Thank you very much!

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.