Measure to calculate percentile for increasing values

[username99880]

Hi, I need help with a calculation that is very difficult for me personally. I have a table with a datetime column and columns with numeric values. I need to calculate the percentile for 95% of the values, but not a simple one, but only for increasing values.
RAMP - MAXIMUM (This is exactly the setting installed on the equipment with which I compare the values)
Thank you

[v-kpoloju-msft · Accepted solution]

Hi @username99880,
Thank you for reaching out to the Microsoft fabric community forum. 

After thoroughly reviewing the details you provided, I reproduced the scenario again, and it worked on my end. I used it as sample data and successfully implemented it.

Measure for calculated column:

IsIncreasing =

VAR CurrentIndex = 'SensorReadings'[Index]

VAR CurrentValue = 'SensorReadings'[Value]

VAR MaxPrevValue =

    CALCULATE(

        MAX('SensorReadings'[Value]),

        FILTER(

            'SensorReadings',

            'SensorReadings'[Index] < CurrentIndex

        )

    )

RETURN

    IF(CurrentIndex = 1 || CurrentValue >= MaxPrevValue, 1, 0)

Measure:

P95_Increasing =

PERCENTILEX.INC(

    FILTER('SensorReadings', 'SensorReadings'[IsIncreasing] = 1),

    'SensorReadings'[Value],

    0.95

)

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @username99880,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @username99880,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[username99880]

Thank you for your solutions, everyone who answered helped me solve the problem in one way or another, thank you 😃

[Elena_Kalina]

Hi @username99880 

1. Create the Increasing Values Column (as you've done successfully)

IncreasingValue = 
VAR CurrentValue = 'Table'[Value]
VAR PreviousValue =
    CALCULATE(
        MAX('Table'[Value]),
        FILTER(
            'Table',
            'Table'[Datetime] < EARLIER('Table'[Datetime])
        )
    )
RETURN
    IF(CurrentValue > PreviousValue, CurrentValue, BLANK())

2 Corrected 95th Percentile Measure

Percentile_95 = 
VAR FilteredTable = 
    FILTER(
        'Table',
        NOT(ISBLANK('Table'[IncreasingValue]))
    )
VAR ValuesToUse = 
    SELECTCOLUMNS(
        FilteredTable,
        "Values", 'Table'[IncreasingValue]
    )
RETURN
    PERCENTILE.INC(ValuesToUse[Values], 0.95)

Alternative (More Efficient) Single-Measure Solution

Percentile_95_Increasing = 
VAR CurrentTable = 
    FILTER(
        ADDCOLUMNS(
            'Table',
            "IsIncreasing", 
            VAR CurrentValue = 'Table'[Value]
            VAR PrevValue = 
                CALCULATE(
                    MAX('Table'[Value]),
                    FILTER(
                        'Table',
                        'Table'[Datetime] < EARLIER('Table'[Datetime])
                    )
                )
            RETURN 
                CurrentValue > COALESCE(PrevValue, -INFINITY)
        ),
        [IsIncreasing] = TRUE()
    )
RETURN
    PERCENTILE.INC(SELECTCOLUMNS(CurrentTable, "Values", 'Table'[Value]), 0.95)

[bhanu_gautam]

@username99880 

Ensure your data is sorted by the datetime column. This can be done in Power Query or directly in Power BI.

Create a calculated column to identify increasing values. Assuming your numeric column is named Value, you can use the following DAX formula:

IncreasingValue =
VAR CurrentValue = 'Table'[Value]
VAR PreviousValue =
CALCULATE(
MAX('Table'[Value]),
FILTER(
'Table',
'Table'[Datetime] < EARLIER('Table'[Datetime])
)
)
RETURN
IF(CurrentValue > PreviousValue, CurrentValue, BLANK())

Now, create a measure to calculate the 95th percentile of the increasing values:

Percentile_95 =
PERCENTILE.INC(
FILTER(
'Table',
NOT(ISBLANK('Table'[IncreasingValue]))
),
'Table'[IncreasingValue],
0.95
)

[username99880]

Thank you for a such quick responce, calculated column work fine but there are some error with a measure u shared
"Too many arguments were passed to the PERCENTILE.INC function. The maximum number of arguments for this function is 2."