Forum Discussion
How show quantile in matrix
Hello Klasia
PERCENTILE.INC interpolates, so it returns a synthetic value that doesn't actually exist in your data. To get the real row closest to that threshold, build a virtual table inside the measure (SUMMARIZE) it's used only for the calculation, never touches the visual, so no extra rows or slowdown.
Quantile Actual Duration =
VAR Threshold = PERCENTILE.INC('Table'[Time], 0.25)
VAR VirtualTable =
SUMMARIZE(
'Table',
'Table'[ID],
"@Time", CALCULATE(MAX('Table'[Time]))
)
VAR ClosestRow =
TOPN(1, VirtualTable, ABS([@Time] - Threshold), ASC)
RETURN
MAXX(ClosestRow, [@Time])
If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.
Thanks,
Connect with me on:
LinkedIn |
Data With Pankaj - YouTube
Hi Klasia
PERCENTILE.INC returns an interpolated percentile threshold. Therefore, the result does not necessarily correspond to an actual duration stored in the source data.
You can first calculate the percentile threshold and then return the highest existing duration that is less than or equal to that threshold:
Quantile1 Actual Duration =
VAR QuantileThreshold =
PERCENTILE.INC (
'Table'[Time],
0.25
)
RETURN
IF (
ISBLANK ( QuantileThreshold ),
BLANK (),
MAXX (
FILTER (
VALUES ( 'Table'[Time] ),
'Table'[Time] <= QuantileThreshold
),
'Table'[Time]
)
)
For the sample data:
The interpolated percentile threshold is 33,210.25.
The highest duration from the source data below that threshold is 20,309.
The measure respects the current filter context, including dynamically selected date ranges and matrix categories. The task ID does not need to be added to the matrix or to a separate calculated table.
This solution specifically returns the source value immediately below the interpolated threshold. It is therefore slightly different from returning the mathematically nearest value.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.