Forum Discussion
Dynamic Quartile Calculation with Gender Split is not working with multiple selections in one slicer
Super UserHi Adityapsahu - create a dynamic approach that recalculates quartiles based on the filtered dataset.
create below measure:
QuartileDistribution =
VAR TotalEmployees = COUNTROWS(ALLSELECTED(EmployeeData))
VAR EmployeesPerQuartile = DIVIDE(TotalEmployees, 4, 0)
VAR Remainder = MOD(TotalEmployees, 4)
VAR Q1Threshold = EmployeesPerQuartile + IF(Remainder > 0, 1, 0)
VAR Q2Threshold = EmployeesPerQuartile + IF(Remainder > 1, 1, 0) + Q1Threshold
VAR Q3Threshold = EmployeesPerQuartile + IF(Remainder > 2, 1, 0) + Q2Threshold
RETURN
SWITCH(
TRUE(),
RANKX(ALLSELECTED(EmployeeData), [HourlyPay], , ASC, Dense) <= Q1Threshold, "Q1",
RANKX(ALLSELECTED(EmployeeData), [HourlyPay], , ASC, Dense) <= Q2Threshold, "Q2",
RANKX(ALLSELECTED(EmployeeData), [HourlyPay], , ASC, Dense) <= Q3Threshold, "Q3",
"Q4"
)
Hope it works
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Hi Rajendra, Thank you for the response.
I tried creating this measure however, it gave me an error that "A single value for column HourlyPay in the table can not be determined. This can happen when a measure formula refers to a column that contains many values without an aggregation."
Hi Adityapsahu ， rajendraongole1 Thank you for you prompt reply!
Replace [HourlyPay] with CALCULATE(SUM('Table'[HourlyPay])) to compare the result.
You could also upload the sample file for better testing.
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.