Forum Discussion
How show quantile in matrix
Hi,
I have a table containing data with information about the task date, category, task ID, and task duration.
| Date | Category | ID | Time |
| 2026-01-01 | A | 32548A | 422593 |
| 2026-07-10 | A | 10349C | 166180 |
| 2026-04-08 | A | 20346A | 80608 |
| 2026-04-15 | A | 87035C | 71914 |
| 2026-03-20 | A | 60354A | 20309 |
| 2026-02-28 | A | 54980 | 12555 |
In Power BI, I would like to add a quantile value to a matrix displaying task data.
However, when I add a quantile calculated using the measure below, I get the quantile threshold value for the corresponding dataset.
quantile1 = PERCENTILE.INC('Table'[Time],0.25) For this data it is 33 210,25
What I actually want to display is the duration of the task (from the source data) that is the calculated quantile value [For this data time - 20 309 ].
I can identify the task ID associated with the required quantile, but I currently need a separate table that includes the task ID to do so.
Is there a way to display the task duration in the matrix instead of the quantile threshold value?
Since users need to be able to change the time range dynamically, all calculations must be implemented using measures.
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
4 Replies
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 - YouTubeHi 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.
Hi User,
PERCENTILE.INC() returns the interpolated percentile value, which is why you're seeing 33,210.25 instead of an actual task duration from your dataset. This is expected behavior.
If you want to display the actual task duration that corresponds to the percentile (rather than the interpolated value), you'll need a measure that:
Calculates the percentile threshold.
Finds the task duration closest to (or equal to) that threshold.
Returns that duration.
For example:
Quantile Duration = VAR Pct = PERCENTILEX.INC( ALLSELECTED('Table'), 'Table'[Time], 0.25 ) RETURN MINX( TOPN( 1, ALLSELECTED('Table'), ABS('Table'[Time] - Pct), ASC ), 'Table'[Time] )This measure returns the actual value from the source data that is closest to the 25th percentile while respecting the current filter context (such as a user-selected date range).
If you need the corresponding Task ID for another calculation, you can use a similar TOPN() approach, but you don't need to include the ID in the matrix itself.
For more information:
PERCENTILEX.INC: https://learn.microsoft.com/dax/percentilex-inc-function-dax
ALLSELECTED: https://learn.microsoft.com/dax/allselected-function-dax
Could you also clarify:
Do you want the closest task duration to the percentile, or the largest value less than or equal to the percentile?
Is the matrix grouped by Category, Date, or another field?
That will help determine the most appropriate DAX pattern for your scenario.
💡 Helpful? Give a Kudos 👍 — keep the community growing.
✅ Solved your issue? Mark this as the Accepted Solution ✔️
Best regards,
Prince Singh | Data Science & Microsoft Fabric EnthusiastHi Klasia,
Thank you for reaching out to Microsoft Fabric Community.
Thank you pankajnamekar25, Ritaf1983 and Prince0011 for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
