Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Quartile calculation with multiple conditions

Hello All,  I have searched for the solution on this, and I have tried multiple things. However, I have not been successful.  I am trying to calculate the quartile for Hours and Quantity in the belo...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

lbendlin , I have modified the original post to include an Excel example.  I hope that provides enough detail.  If not, let me know.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    It would be something like this

    Hours Quartile = 
SWITCH(TRUE(),
sum('Table'[Sum of Hours])<calculate(PERCENTILE.INC('Table'[Sum of Hours],0.25),ALLSELECTED('Table')),1,
sum('Table'[Sum of Hours])<calculate(PERCENTILE.INC('Table'[Sum of Hours],0.5),ALLSELECTED('Table')),2,
sum('Table'[Sum of Hours])<calculate(PERCENTILE.INC('Table'[Sum of Hours],0.75),ALLSELECTED('Table')),3,
4)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks Ibendlin.  I am still having an issue with the percentile pieces: 

      calculate(PERCENTILE.INC('Table'[Sum of Hours],0.25),ALLSELECTED('Table'))

      The table that I am referencing has many line items that add up to the total 'sum of hours'.  For instance, the max in the Hours column is 375.  The line above appears to be looking at those individual line items to calculate the quartile vs the summed amount per a 'Unit Number'.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        3 years ago

        Please provide sanitized sample data that fully covers your issue.
        Please show the expected outcome based on the sample data you provided.