Forum Discussion
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( SUM(Q1_Subledger[Amount incl VAT]), FILTER(Q1_Subledger,Q1_Subledger[Source.Name]=MAX(Q1_Subledger[Source.Name]) ))
I am looking for something that will return the second biggest value... any idea? Name of the table is Q1_Subledger. So, from below dataset, I would like to have a sum of "Amount incl VAT" for 201903_6120.xlsx. And, when I add a set for 201905_6120.xlsx, I'd like the formula to pick 201904_6120 (so second biggest value).
| Source.Name | Amount incl VAT |
| 201901_6120.xlsx | 5000 |
| 201901_6120.xlsx | 654 |
| 201901_6120.xlsx | 2001 |
| 201901_6120.xlsx | 225 |
| 201901_6120.xlsx | 23 |
| 201901_6120.xlsx | 5564 |
| 201901_6120.xlsx | 58797 |
| 201902_6120.xlsx | 46461 |
| 201902_6120.xlsx | 0 |
| 201902_6120.xlsx | 45468 |
| 201902_6120.xlsx | 645461 |
| 201902_6120.xlsx | 5456874 |
| 201902_6120.xlsx | 163546 |
| 201902_6120.xlsx | 500 |
| 201903_6120.xlsx | 600 |
| 201903_6120.xlsx | 987 |
| 201903_6120.xlsx | 415 |
| 201903_6120.xlsx | 525 |
| 201903_6120.xlsx | 858 |
| 201903_6120.xlsx | 747 |
| 201903_6120.xlsx | 969 |
| 201904_6120.xlsx | 636 |
| 201904_6120.xlsx | 302 |
| 201904_6120.xlsx | 301 |
| 201904_6120.xlsx | 201 |
| 201904_6120.xlsx | 502 |
thank you!
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] ) ) )
2 Replies
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] ) ) )Guy, you are amazing! I had solved my problem with your code! Thanks!
