6 Replies Latest reply on Jun 11, 2018 8:51 AM by Keir Blockley

# Help calculating dynamic quartiles

Hi

Please can someone help with calculating quartiles. The quartile would be based on the "hourly rate" column, but the quartile that a row is in may change if the "team" filter is used.

I have attached a workbook with an example data set.

I would like to be able to do a percentage split by gender within each quartile.

Please see below picture showing the values for quartiles, first table for "all teams" and then for "team A"

Keir

• ###### 1. Re: Help calculating dynamic quartiles

Could you save this in 10.2 or 10.3? I'm unfortunately not on the newer version of tableau yet. Or you could post the data as well.

Having said that, you could look to utilize the PERCENTILE() function.

Example from Superstore. I created this Calculated Field and converted to Dimension:

IF [Sales] <= {fixed: PERCENTILE([Sales], .25)} THEN 1

ELSEIF [Sales] <= {fixed: PERCENTILE([Sales], .5)} THEN 2

ELSEIF [Sales] <= {fixed: PERCENTILE([Sales], .75)} THEN 3

ELSEIF [Sales] <= {fixed: PERCENTILE([Sales], 1)} THEN 4

END

Then I simply put on Number of Records and changed to Running Total:

Adding a secondary table calculation for reassurance:

Hope this helps!

Bryce

• ###### 2. Re: Help calculating dynamic quartiles

Bryce

Yes it has helped thank you.

Is there a feature in tableau to make the quartiles have an even amount of records? I can understand why it has apportioned the records as it has though, as there are multiple records with the same hourly rate, so it doesn't "neatly" fit into 4 quarters.

Thanks

Keir

• ###### 3. Re: Help calculating dynamic quartiles

Hmm. Sorry, I'm not really sure I follow. What do you mean an "even" amount of records? Can you provide an example?

And glad it at least got you started!

• ###### 4. Re: Help calculating dynamic quartiles

Bryce

I meant that so that each quartile has an equal amount of records within each one e.g. if there are 400 records in total, there would be 100 in each quartile.

The issue is that, as my data has lots of records with the same hourly rates, it doesn't split the quartiles evenly.

Keir

• ###### 5. Re: Help calculating dynamic quartiles

Ah, I see. This is difficult and not sure I would really refer to these as 'quartiles' then, at least not in the statistical sense.

Unfortunately there's no level of detail expression for Rank. You can do it on the view if showing all records, but you can't store it in the data. I might suggest doing this prior to bringing it in. If you can sort your data by Hourly Rates and assign IDs in this way (by Gender if desired), then you could do it in that way.

{fixed [Gender]: MAX([ID])} returns Max ID in the data per each gender. And then you can do something like:

{fixed [Gender]: MAX(

IF [ID] < [Max ID per Gender]*.25 THEN 1

ELSEIF [ID] < [Max ID per Gender]*.5 THEN 2

ELSEIF [ID] < [Max ID per Gender]*.75 THEN 3

ELSE 4

END)}

That might work and return the group per each record by gender.

Bryce

• ###### 6. Re: Help calculating dynamic quartiles

Bryce

I don't think I can do the above, as the dashboard will have multiple filters, which will change which quartile a record will be in potentially. I think I am expecting too much for it to do the quartiles perfectly.

Thanks for your help with this, I am using the solution in your original response.

Keir