Quartiles - dynamically recalculating with slicers

[arthau1]

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

[sjoerdvn]

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.

[Kedar_Pande · Accepted solution]

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
)