measure reflecting 2nd largest value of another column

[INSP7791]

Hi,

My table looks like this, I want to create a measure which based on selected GEO, it would return the Industry by the 2nd largest Current Year (000s) value.

tried this

topIndustry= CALCULATE (
top ( 'CMA-Industry'[Industry], TRUE () ),
FILTER (
'CMA-Industry',
'CMA-Industry'[Current Year (000s)] = MAXX(TOPN(2, SUMMARIZE('Current Year (000s)', 'CMA-Industry'[Current Year (000s)] )))
)
)

[INSP7791]

should I added 

should I add this, then

Var _1 = selectedvalue(CMA-Industry[GEO])
FILTER ( Max2Table, 'CMA-Industry'[GEP] = Var_1)

[tamerj1]

Hi @INSP7791 

please try

topIndustry =
VAR Max2Table =
    TOPN ( 2, 'CMA-Industry', 'CMA-Industry'[Current Year (000s)] )
VAR SecondLargest =
    MINX ( Max2Table, 'CMA-Industry'[Current Year (000s)] )
RETURN
    MAXX (
        FILTER ( Max2Table, 'CMA-Industry'[Current Year (000s)] = SecondLargest ),
        'CMA-Industry'[Industry]
    )

[INSP7791]

Thank you!  I think it's almost good.  Is there a way to also filter by selected GEO as on the page I have a filter allowing users to select the GEO of their choice, this measure should change as per the filter.

[tamerj1]

@INSP7791 

It should work without having to add additional filters.