Power bi DAX findout 2nd MAX value

[BIswajit_Das]

Hello Guys,
I have a table like

item	res	a
1	a	30.98
1	b	30.98
1	c	20.34
1	d	17.7

and
my required table is like

item	res	a	max	2nd	gap
1	a	30.98	30.98	30.98	0
1	b	30.98	30.98	30.98	0
1	c	20.34	30.98	30.98	0
1	d	17.7	30.98	30.98	0

when i calculated its hard to detect max cause both a and b have same values so the coming 2nd max result is wrong
i.e the coming result is like

item	res	a	max	2nd	gap
1	a	30.98	30.98	20.34	10.64
1	b	30.98	30.98	20.34	10.64
1	c	20.34	30.98	20.34	10.64
1	d	17.7	30.98	20.34	10.64

Thanks & Regrads...

[BIswajit_Das]

#EP_MAX_PARTYCOUNT% =

VAR _value =

    MAXX (

        FILTER (

            SUMMARIZE (

                'Z_EP_DATA',

                'Z_EP_DATA'[PC_ID],

                'Z_EP_DATA'[RESPONSE1],

                "@count",

                COUNTROWS ( 'Z_EP_DATA' )

            ),

            'Z_EP_DATA'[PC_ID] = EARLIER('Z_EP_DATA'[PC_ID])

        ),

        [@count]

    )

VAR _total =

CALCULATE (

    DISTINCTCOUNT( 'Z_EP_DATA'[MOBILE] ),

    FILTER (

        'Z_EP_DATA',

        'Z_EP_DATA'[PC_ID] = EARLIER ( 'Z_EP_DATA'[PC_ID] )

        )

    )

RETURN

    DIVIDE(_value,_total,0)

[BIswajit_Das · Accepted solution]

Hello I've finally figures it out 
Here's the used related DAX

VAR _max_count =

    MAXX (

        SUMMARIZE (

            'table_name',

            'table_name'[item],

            'table_name'[res],

            "count",

            COUNTROWS ( 'table_name' )

        ),

        [count]

    )

VAR _value =

    MAXX (

        FILTER (

            SUMMARIZE (

                'table_name',

                'table_name'[item],

                'table_name'[res],

                "@count",

                COUNTROWS ( 'table_name' )

            ),

            [@count] = _max_count

        ),

        'table_name'[res]

    )

VAR _sec_max_count =

    MAXX (

        FILTER (

            SUMMARIZE (

                'table_name',

                'table_name'[item],

                'table_name'[res],

                "count2",

                COUNTROWS ( 'table_name' )

            ),

            'table_name'[res] <> _value &&

            'table_name'[item] = EARLIER('table_name'[item])

        ),

        [count2]

    )

VAR _total =

CALCULATE (

    COUNT ( 'table_name'[res] ),

    FILTER (

        'table_name',

        'table_name'[item] = EARLIER ( 'table_name'[item] )

        )

    )

RETURN

    DIVIDE(_sec_max_count,_total,0)

[BIswajit_Das]

Hello @Ahmedx thanks for responding but

my required result table should like

item	res	a	max	2nd	gap
1	a	30.98	30.98	30.98	0
1	b	30.98	30.98	30.98	0
1	c	20.34	30.98	30.98	0
1	d	17.7	30.98	30.98	0

[Greg_Deckler]

@BIswajit_Das Try:

max a 2nd = 
    VAR __Item = MAX('Table'[item])
    VAR __Table = FILTER(ALL('Table'), [item] = __Item)
    VAR __Summarized = SUMMARIZE(__Table, [item], [a], "__Count", COUNTROWS('Table'))
    VAR __Max = MAXX(__Table, [a])
    VAR __Result = 
        IF(
            MAXX(FILTER(__Summarized, [a] = __Max),[__Count]) > 1,
            __Max,
            MAXX(FILTER(__Summarized, [a] < __Max), [a])
        )
RETURN
    __Result

[BIswajit_Das]

Hello @Greg_Deckler  Thanks for responding but what if i have multiple Items
i.e

item	res	a	max	2nd	gap
1	a	30.98	30.98	30.98	0
1	b	30.98	30.98	30.98	0
1	c	20.34	30.98	30.98	0
1	d	17.7	30.98	30.98	0
2	a	55	55	45	10
2	b	45	55	45	10
3	a	35	65	35	30
3	c	65	65	35	30

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Power bi DAX findout 2nd MAX value.pbix