Dynamic Quartile Calculation with Gender Split is not working with multiple selections in one slicer

[Adityapsahu]

I want to display a quartile calculation from a filtered dataset based on a slicer selection. When I select a single selection on Legal entity the Total Numbers are distributed correctly but with the multiple selection, it doesn’t distribute it correctly.

There are a few scenarios shown below of how we want to split the quartiles.I have created the “Dynamic Quartile Agency” where its distributes the numbers based on the below scenario but it's just that it doesn’t work on the multiple slicer filter selection.

Scenarios:

Divide the list into quarters

Divide this list into 4 quarters, with an equal number of employees in each section.

These quarters are:

• upper hourly pay quarter – Q4
• upper middle hourly pay quarter – Q3
• lower middle hourly pay quarter – Q2
• lower hourly pay quarter – Q1

If the number of your employees is not divisible by 4, distribute them as follows:

• If there is no employee left over, then distribute equally between 4 quartiles (Q1,Q2,Q3,Q4)

• if there is one employee left over, add them to the lower hourly pay quarter (Q1)
• if there are 2 employees left over, add one to the lower hourly pay quarter and one to the upper middle hourly pay quarter (Q1 and Q3)
• if there are 3 left over, distribute them between lower, lower middle and upper middle pay quarters (Q1,Q2,Q3)

[rajendraongole1]

Hi @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!!

[Adityapsahu]

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."

[Anonymous]

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.