Rank highest to lowest value based on another measure

[CEvange]

I'm not sure if this is possible but I need some help creating a measure(s) (due to access restriction I can only add in measures).

I have 2 existing measures - Total SI and Rolling 30D Events:

Total SI = CALCULATE(COUNTA(Event[EventSK]),Event[Is Significant Incident Flag]=1) 

Rolling 30D Events = CALCULATE(DISTINCTCOUNT('Event'[EventSK]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]), -30,DAY),ALL('Date'))  

For each day where an SI is not blank, I need to get the equivalent Rolling 30D Event value then rank that from highest to lowest similar to the table below:

Once I have the ranking, out of my total SIs in the last 12 months, I need to get the Rolling 30D Event value at 75%. In the table below, how can I get the value of 118?

Appreciate all the help I can get

[CEvange · Accepted solution]

Update: I was able to do this by adding the initial measure suggested by @123abc :

Rolling 30D Events with SI =
VAR Rolling30DEvents =
CALCULATE(
[Rolling 30D Events],
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
&& 'Date'[Date] > MAX('Date'[Date]) - 30
)
)
RETURN
IF(ISBLANK([Total SI]), BLANK(), Rolling30DEvents)

Then to rank the events I used this 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)))

[123abc]

To achieve what you're describing, you can follow these steps:

1. Calculate Daily Rolling 30D Events for Days with SI: Create a measure that calculates the rolling 30-day events for days with significant incidents. This measure should filter dates where the SI is not blank.

Rolling 30D Events with SI =
VAR Rolling30DEvents =
CALCULATE(
[Rolling 30D Events],
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
&& 'Date'[Date] > MAX('Date'[Date]) - 30
)
)
RETURN
IF(ISBLANK([Total SI]), BLANK(), Rolling30DEvents)

Rank the Rolling 30D Events: Next, create a measure to rank the rolling 30-day events for days with significant incidents.

Rank Rolling 30D Events =
RANKX(
FILTER(
ALL('Date'),
'Date'[Date] = MAX('Date'[Date])
),
[Rolling 30D Events with SI],
,
DESC,
Dense
)

Retrieve Rolling 30D Event Value at 75%: Now, you can create a measure to retrieve the rolling 30-day event value at the 75th percentile.

Rolling 30D Events at 75% =
VAR TotalSIsLast12Months = [Total SI]
VAR Percentile = 0.75
RETURN
PERCENTILEX.INC(
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
&& 'Date'[Date] >= MAX('Date'[Date]) - 365
),
[Rolling 30D Events],
Percentile * TotalSIsLast12Months
)

These measures should help you rank the rolling 30-day events and retrieve the value at the 75th percentile as per your requirements. Adjust the date ranges and conditions based on your specific data model if needed.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[CEvange]

When I created the Rank Rolling 30D Event measure it just gave a value of 1 for everything.