topic Re: Rank highest to lowest value based on another measure in DAX Commands and Tips

Rank highest to lowest value based on another measure

CEvange — Thu, 01 Feb 2024 02:38:19 GMT

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

Re: Rank highest to lowest value based on another measure

123abc — Thu, 01 Feb 2024 05:32:42 GMT

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

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.

Re: Rank highest to lowest value based on another measure

CEvange — Thu, 01 Feb 2024 07:47:02 GMT

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

Re: Rank highest to lowest value based on another measure

CEvange — Tue, 13 Feb 2024 04:54:00 GMT

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

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