Forum Discussion
Quartiles - dynamically recalculating with slicers
Hello hello!
There are a couple of topics similar to this, but none address my issue, so creating a new thread.
Very simple request on paper, to calculate quartiles based on value, PER year. The part I am struggling with is that it calcualtes it once, correctly, but then when I use slicers in my report, it doesn't re-calculate, but just takes those originally calculated quartile values and slots them into Q1, Q2, Q3, Q4, which creates uneven quartile distribution.
So far I have used PERCENTILE.INC(Main[Hourly], 0.25), with a FILTER on [Year], by using EARLIER function.
This works, up until I use any slicers in the visual later. Tried ALLSELECTED, didn't work they way I used it.
GPT & Gemini always suggest something that works except with slicers.
Needs to be dynamic, so I was thinking a measure, instead of calc. column, but measures cannot be used as dimensions as far as I know.
Thank you!
| EmployeeID | Function | Year | Hourly |
| E1 | Commercial | 2022 | 1000.00 |
| E2 | Commercial | 2022 | 90.00 |
| E3 | Commercial | 2022 | 80.00 |
| E4 | Finance | 2022 | 70.00 |
| E5 | Finance | 2022 | 60.00 |
| E6 | HR | 2022 | 50.00 |
| E7 | HR | 2022 | 40.00 |
| E8 | Purchasing | 2022 | 10.00 |
| E9 | Purchasing | 2022 | 5.00 |
| E1 | Commercial | 2023 | 1000.00 |
| E2 | Commercial | 2023 | 800.00 |
| E3 | Commercial | 2023 | 700.00 |
| E4 | Finance | 2023 | 400.00 |
| E5 | Finance | 2023 | 300.00 |
| E6 | HR | 2023 | 200.00 |
| E7 | HR | 2023 | 100.00 |
| E8 | Purchasing | 2023 | 100.00 |
| E9 | Purchasing | 2023 | 70.00 |
You can create separate measures for each quartile
Q1 Measure =
PERCENTILE.INC(
FILTER(
Main,
Main[Year] = MAX(Main[Year])
),
0.25
)
Q2 Measure =
PERCENTILE.INC(
FILTER(
Main,
Main[Year] = MAX(Main[Year])
),
0.50
)
Q3 Measure =
PERCENTILE.INC(
FILTER(
Main,
Main[Year] = MAX(Main[Year])
),
0.75
)
2 Replies
You can create separate measures for each quartile
Q1 Measure =
PERCENTILE.INC(
FILTER(
Main,
Main[Year] = MAX(Main[Year])
),
0.25
)
Q2 Measure =
PERCENTILE.INC(
FILTER(
Main,
Main[Year] = MAX(Main[Year])
),
0.50
)
Q3 Measure =
PERCENTILE.INC(
FILTER(
Main,
Main[Year] = MAX(Main[Year])
),
0.75
)Indeed, calculated columns will not work and you need to use measures. If you need columns or axis to display results on you will also need to create a "helper" dimension, basically a small table with hardcoded vales (Q1, Q2, etc).
In these measure you will need to get the value from the helper dimension in the current context, and filter your result accordingly.
