Ranking a Measure by a Row Item

[PeteSil]

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.

[grazitti_sapna]

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 · Accepted solution]

@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

[PeteSil]

This worked. Thank you very much!