Forum Discussion

VISRAI's avatar
VISRAI
New Member
2 years ago
Solved

2nd Highest Value

There are 3 columns in table, company, model and sale. There are 2 company name and different models for them, how can i get the 2nd highest sale model. I want the model name which have the 2nd highe...
Need Help
  • VISRAI's avatar
    VISRAI
    2 years ago

    i have created a measure Rank = RANKX(ALLEXCEPT('Table','Table'[Class Name]),CALCULATE(SUM([EPI CNV 100]),ALLEXCEPT('Table','Table'[Class Name],'Table'[Insured Name])),,DESC,Dense) then used this one in the filter to get only 2nd highest value

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

VISRAI Should be something along the lines of:

Measure = 
  VAR __Table = SUMMARIZE( 'Table', [model], "__Value", MAX('Table'[sale]) )
  VAR __MaxSale = MAXX( __Table, [__Value] )
  VAR __2ndMaxSale = MAXX( FILTER( __Table, [__Value] <> __MaxSale ), [__Value] )
  VAR __Result = MAXX( FILTER( __Table, [__Value] = __2ndMaxSale ), [model] )
RETURN
  __Result