Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Return second biggest value

Hi!   I am looking for a formula that will return the second biggest value.    Below is the formula that is returning me the biggest value from a certain dataset:   LAstMonth = CALCULATE( SU...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous 

     

    Try following measure

     

    SecondLAstMonth =
SUMX (
    TOPN (
        1,
        TOPN ( 2, VALUES ( Q1_Subledger[Source.Name] ), [Source.Name], DESC ),
        [Source.Name], ASC
    ),
    CALCULATE ( SUM ( Q1_Subledger[Amount incl VAT] ) )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

Anonymous 

 

Try following measure

 

SecondLAstMonth =
SUMX (
    TOPN (
        1,
        TOPN ( 2, VALUES ( Q1_Subledger[Source.Name] ), [Source.Name], DESC ),
        [Source.Name], ASC
    ),
    CALCULATE ( SUM ( Q1_Subledger[Amount incl VAT] ) )
)