Forum Discussion

Adityapsahu's avatar
Adityapsahu
New Member
2 years ago

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

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)
Interesting Links
Need Help
Show and Tell
Tips and Tricks

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Adityapsahu
      New Member
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.