Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
@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!
@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] ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |