Measure that displays the 2nd ranked value

[rocbotman]

Hello, 

I'm trying to get a measure in a matrix table that displays the largest market share within certain categories. If they are already ranked 1, I want to display the next to highest market share. 

So far all the ranks 2 or higher, the number 1 rank for market share should return: 39,32%, but in the row where there is the number 1 market share, i want the 2nd market share to be returned: 12,29%.

I have the following measure, but when I put it in my  matrix table it won't work. 

_Potentie_marktaandeel =

IF (

    [_Rank_marktaandeel] <> 1,

    [_MaxMarktaandeel],

CALCULATE([_Aantal_marktaandeel],

FILTER(

    ALL(FEIT_HERKOMST_MBO),

    [_Rank_marktaandeel]=2

)

))

Here is a link to all the measures I used for this table: 
https://docs.google.com/document/d/1pBmx0n7TYuahbLqY-cWP2dT7aqW8DZoscBrE-LsQJWM/edit?usp=sharing

Since I'm using dynamic parameters it needs to be a measure. 

Thanks for the help.

jaar	PROVINCIE	Subgroep	Instelling	Sum of aantal	_Aantal_omvang	_Aantal_marktaandeel	_Rank_marktaandeel	_MaxMarktaandeel	_Aantal_marktomvang	Desired output
2022	Utrecht	Bouwkunde	ROC Midden Nederland	464	464	39,32%	1	39,32%	1180	12,29%
2022	Utrecht	Bouwkunde	MBO Amersfoort	145	145	12,29%	2	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	MBO Utrecht	138	138	11,69%	3	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	Hoornbeeck College	109	109	9,24%	4	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	ROC A12	70	70	5,93%	5	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	ROC van Amsterdam	57	57	4,83%	6	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	mboRijnland	48	48	4,07%	7	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	Rijn IJssel	39	39	3,31%	8	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	SOMA College	28	28	2,37%	9	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	ROC Da Vinci College	23	23	1,95%	10	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	ROC Rivor	21	21	1,78%	11	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	ROC Nova College	11	11	0,93%	12	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	Koning Willem I College	7	7	0,59%	13	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	Curio	6	6	0,51%	14	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	Deltion College	4	4	0,34%	15	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	Stichting Landstede	3	3	0,25%	16	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	Summa College	2	2	0,17%	17	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	ROC Horizon College	1	1	0,08%	18	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	ROC Nijmegen eo	1	1	0,08%	18	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	ROC van Flevoland	1	1	0,08%	18	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	ROC van Twente	1	1	0,08%	18	39,32%	1180	39,32%
2022	Utrecht	Bouwkunde	Zadkine	1	1	0,08%	18	39,32%	1180	39,32%

[v-cgao-msft · Accepted solution]

Hi @rocbotman ,

Please try:

_Potentie_marktaandeel = 
var _max = MAXX(SUMMARIZE(ALLSELECTED('Table'),'Table'[jaar],'Table'[PROVINCIE],'Table'[Subgroep],'Table'[Instelling]),[_Aantal_marktaandeel])
VAR _second_max = MAXX(FILTER(SUMMARIZE(ALLSELECTED('Table'),'Table'[jaar],'Table'[PROVINCIE],'Table'[Subgroep],'Table'[Instelling]),[_Rank_marktaandeel]=2),[_Aantal_marktaandeel])
VAR _result = IF([_Rank_marktaandeel]<>1,_max,_second_max)
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group