How to get the value at 75th percent based on multiple measures

[CEvange]

Can someone please help me create a measure to get a specific value based on multiple measures?

In the table below based on my Index, I need to get the equivalent Rolling30D with SI value sitting at the 75th percentile.

For the sample data below, 75% of 41  would be 31 so I need to be able to retrieve the value 124.

I tried creating a measure myself which I've called Trigger Line however it gives me multiple rows, I just need a single number.

*Trigger Line = VAR _k = 0.25

VAR SUMMARY =

FILTER(ADDCOLUMNS(

    SUMMARIZE('Date','Date'[Date]), "'Rank'[@value]",[* Rolling30D with SI],"'Rank'[Rank2]",[*Index]),

    NOT ISBLANK('Rank'[Rank2]))

    RETURN

    PERCENTILEX.INC(SUMMARY, 'Rank'[@value], _k)

In the table above, the trigger line should only show the value of 124 for all the rows. How can I achieve this?

[CEvange]

Index is also a measure

*Index = IF(ISINSCOPE('Date'[Date]), IF(NOT ISBLANK([* Rolling30D with SI]), RANKX(ALLSELECTED('Date'),[* Rolling30D with SI] + DIVIDE(RANKX(ALLSELECTED('Date'), [*12M SI]), (COUNTROWS(ALL('Date')) + 1)),,DESC,Dense)))

[Anonymous]

HI @CEvange,

You cna try to use the following measure formula if it suitable for your requirement:

formula =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'Date' ),
        'Date'[Date],
        "Rolling30D", [* Rolling30D with SI],
        "Index", [*Index],
        "TL", [*Trigger Line]
    )
VAR percent = 0.75
VAR target =
    ROUNDUP ( percent * COUNTROWS ( summary ), 0 )
RETURN
    MAXX ( FILTER ( summary, [Index] = target ), [TL] )

Regards,

Xiaoxin Sheng

[CEvange]

Hi @Anonymous , 

I tried the measure you provided unfortunately it just came up as blank.