How to calculate which percentile the latest value is in against other values in same category?

[WestWinter]

So I have the below table, and would like to get a formula to find out which percentile my latest value (in this case, the 1/4/2020) is in. How can I find this?

Date	Category	Value
1/1/2020	A	34214
1/2/2020	A	209319
1/3/2020	A	39420
1/4/2020	A	2330

[V-lianl-msft · Accepted solution]

Hi @WestWinter ,

I created a calculated column to calculate the percentile.

Column =
VAR _ROW1 =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            Table1[Value] <= EARLIER ( Table1[Value] )
                && Table1[Category] = EARLIER ( Table1[Category] )
        )
    )
VAR _ROW2 =
    CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Category] ) )
VAR _P = _ROW1 / _ROW2
VAR MAX_DATE =
    CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Category] ) )
RETURN
    IF ( Table1[Date] = MAX_DATE, _P )

Sample .pbix

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

[amitchandak]

@WestWinter ,

Try like divide(lastnonblankvalue(Table[Date],sum(Table[Value])),sum(Table[Value]))

Plot against category

for date use max or lastnonblank or last

[Fowmy]

@WestWinter 

Do you also need the percentile per category?

Thanks

[WestWinter]

No. Just how the latest value (or all values) fair against all other values in the same category.

[Fowmy]

@WestWinter

Percentile Latest Value = 

VAR _DATE = CALCULATE(MAX(TABLE01[Date]),ALL(TABLE01))
VAR _VALUE = CALCULATE( SUM(TABLE01[Value]), TABLE01[Date] = _DATE, ALL(TABLE01)) 
    
RETURN
COALESCE(
    DIVIDE(
        CALCULATE(
            COUNTROWS(TABLE01),
            FILTER(
                ALL(TABLE01),
                TABLE01[Value] < _VALUE
            )
        ),

            COUNTROWS(ALL(TABLE01))
    )
    ,0
)

You can use per row dynamic measure as below:

Percentile Latest Value Per Row = 

VAR _VALUE = SUM(TABLE01[Value])

RETURN
COALESCE(
    DIVIDE(
        CALCULATE(
            COUNTROWS(TABLE01),
            FILTER(
                ALL(TABLE01),
                TABLE01[Value] < _VALUE
            )
        ),

            COUNTROWS(ALL(TABLE01))
    )
    ,0
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[WestWinter]

I tried your DAX code for my measure, but it returned me all 1.00 for all categories... strange