Quartiles Dropping off when certain Filters are applied.

[jackcoxer]

Good morning, 

We have a report that uses the below to calculate the Employee quartile rankings based on performance. This works 95% of the time, however, sometimes when certain filters are applied the final quartile drops off the report. 

We use the below dax to calculate the Quartile:-

Employee Attachment Rate Quartile = 
VAR __q1 = PERCENTILEX.INC ( ALLSELECTED ( Employees[Employee] ), [Employee Attachment Rate Rank], .25 )
VAR __q2 = PERCENTILEX.INC ( ALLSELECTED ( Employees[Employee] ), [Employee Attachment Rate Rank], .50 )
VAR __q3 = PERCENTILEX.INC ( ALLSELECTED ( Employees[Employee] ), [Employee Attachment Rate Rank], .75 )
VAR __rank = [Employee Attachment Rate Rank]
RETURN
IF(HASONEFILTER(Employees[Employee]),
    CALCULATE(SWITCH ( TRUE(),
    __rank > __q3, 4,
    __rank > __q2, 3,
    __rank > __q1, 2,
    1
)))

We then use the below to get t he average attachment rate per quartile:-

Employee Attachment Rate Quartile Avg Rate = 

IF( HASONEFILTER(Quartile[Quartile]),
AVERAGEX(
	KEEPFILTERS(FILTER(ADDCOLUMNS(
        	Filter(VALUES(Employees[Employee]),[Employee VAPS Opportunities Combined] > 9),
      		"@Quartile",	CALCULATE([Employee Attachment Rate Quartile]),
          	"@Rate",		CALCULATE([Employee Attachment Rate]),
          	"@Rank",		CALCULATE([Employee Attachment Rate Rank]),
          	"@Sales",		CALCULATE([Employee Attachment Count]),
          	"@Opps",		CALCULATE([Employee VAPS Opportunities Combined])),
            [@Quartile] = SELECTEDVALUE ( Quartile[Quartile] ))),[@Rate]))

This may have been an on going issue, but we have occasions when the data is filtered down on the date/employees table that the final quartile "4" is dropping off the table.

On paper these calculations should work and as long as there is 4 or more employees selected there should always be 4 quartiles but i'm struggling to find out what is causing the issue. For context, there will never normally be less then 300 employees selected at once.

[Anonymous]

Hi @jackcoxer,

Here are a few steps to diagnose and potentially resolve the issue:

1.Examine the Data Context: When filters are applied, they change the context in which the DAX calculations are evaluated. Ensure that the filters applied do not inadvertently exclude all of the employees that would fall into the fourth quartile.

2.Check for Blank or Error Values: Ensure that there are no blank or error values in the column, as this could impact the quartile calculation.

3.Consider Data Volume and Distribution: When dealing with a large number of employees (300+), ensure that the data distribution is such that the fourth quartile indeed has values. In some cases, if the values are heavily skewed, the fourth quartile might have very few or no employees falling into it, depending on the filters applied.

4.Debug Using Sample Data: Isolate the issue by creating a smaller sample of your data that reproduces the problem. This can make it easier to debug and understand what's happening when the fourth quartile disappears.

5.Use Error Handling: Implement some error handling in your DAX formulas to check for conditions that lead to the disappearance of the fourth quartile. For example, you could add a measure that checks if the fourth quartile is present and returns a message if not.

6.Try converting your measures to an integer or currency type.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.