Forum Discussion
Dynamic quantile calculation and matrix marking
Hi. I have in my Power BI matrix, where from each category and type I want show in which row i have median and quantiles (0,25; 0,95).
All value in time I have coating in measure because must be dinamic, depence what you choose in filters
My matrix:
| Typ | Category | Task | Time A [s] |
| Type A | |||
| Category A | |||
| Task 1 | 25 | ||
| Task 2 | 36 | ||
| Task 3 | 20 | ||
| Category B | |||
| Task 1 | 10 | ||
| Type B | |||
| Category C | |||
| Task 1 | 5 | ||
| Task 2 | 3 | ||
| Task 3 | 8 | ||
| Task 4 | 20 | ||
| Task 5 | 9 | ||
| Task 6 | 17 |
And I Want add columns like
| Typ | Category | Task | Time A [s] | Median | Quantile 0.25 | Quantile 0.95 |
| Type A | ||||||
| Category A | ||||||
| Task 1 | 25 | Median | Quantile 0.25 | |||
| Task 2 | 36 | Quantile 0.95 | ||||
| Task 3 | 20 | |||||
| Category B | ||||||
| Task 1 | 10 | Median | Quantile 0.25 | Quantile 0.95 | ||
| Type B | ||||||
| Category C | ||||||
| Task 1 | 5 | Quantile 0.25 | ||||
| Task 2 | 3 | |||||
| Task 3 | 8 | Median | ||||
| Task 4 | 20 | |||||
| Task 5 | 9 | |||||
| Task 6 | 17 | Quantile 0.95 |
I don't know how to compare values at the task level with the value calculated at the category level
Hi Klasia,
Thank you for reaching out to the Microsoft Fabric Forum Community.
Thank you pankajnamekar25, for your valuable insights.
I have reproduced the scenario using sample data. Please find the attached pbix file for your reference.
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.
6 Replies
Hello Klasia
You have to create one measure of each calculations.
Median Calculation
Median Time =
CALCULATE(
MEDIAN(Tasks[Time A [s]]),
ALLEXCEPT(Tasks, Tasks[Category])
)
0.25 Quantile Calculation (25th Percentile)
DAX doesn’t have a direct quantile function, but you can create a measure using PERCENTILEX.INC:
Quantile 0.25 =
CALCULATE(
PERCENTILEX.INC(
FILTER(Tasks, NOT(ISBLANK(Tasks[Time A [s]]))),
Tasks[Time A [s]],
0.25
),
ALLEXCEPT(Tasks, Tasks[Category])
)
0.95 Quantile Calculation (95th Percentile)
Quantile 0.95 =
CALCULATE(
PERCENTILEX.INC(
FILTER(Tasks, NOT(ISBLANK(Tasks[Time A [s]]))),
Tasks[Time A [s]],
0.95
),
ALLEXCEPT(Tasks, Tasks[Category])
)
Mark Median
Mark Median =
IF(
SELECTEDVALUE(Tasks[Time A [s]]) = [Median Time],
"Median",
BLANK()
)
Mark Quantile 0.25
Mark Quantile 0.25 =
IF(
SELECTEDVALUE(Tasks[Time A [s]]) <= [Quantile 0.25],
"Quantile 0.25",
BLANK()
)
Mark Quantile 0.95
Mark Quantile 0.95 =
IF(
SELECTEDVALUE(Tasks[Time A [s]]) >= [Quantile 0.95],
"Quantile 0.95",
BLANK()
)
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
Thanks pankajnamekar25 , but I don't know what I do wrong in Median Time, because show me wrong value
And I do exactly how you wrote
EDIT: I chage ALLEXCEPT to ALLSELECTED and now working.
Hi Klasia,
Thank you for reaching out to the Microsoft Fabric Forum Community.
Thank you pankajnamekar25, for your valuable insights.
I have reproduced the scenario using sample data. Please find the attached pbix file for your reference.
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.
Hi Klasia,
We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.
Thank you.
Hi Klasia,
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.
Hi Klasia,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
