topic How to get the value at 75th percent based on multiple measures in DAX Commands and Tips

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

CEvange — Tue, 13 Feb 2024 06:07:48 GMT

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?

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

CEvange — Tue, 13 Feb 2024 06:20:29 GMT

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)))

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

Anonymous — Wed, 14 Feb 2024 01:39:42 GMT

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

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

CEvange — Wed, 14 Feb 2024 04:01:24 GMT

Hi Anonymous ,

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