Context: My tableau dataset is a Google BigQuery table which I connect directly too; it contains several hundred million rows so extracting it doesn't seem feasible. This is important because it means I'm unable to use the 'Percentile' function which seems to be the linchpin for many solutions I've seen so far.
I have a dataset which contains account level records, and the number of times they've activated a product. I would like to categorise each of these customer records into a quantile (let's say percentile in this instance), based on the number of times they've used the product (Product_Usage field). I would like this quantile field to recalculate itself with each filter I apply. i.e. if I filter to the 'IE' customers in the country_code field; I would like the quantiles to recalculate against the filtered records to reflect the total number of 'IE' customers, rather than the total base number.
I've attached a workbook with a dummy data set which hopefully helps visualise the structure of my data and ideal output. The end visualisation I'd like to get to is displayed in 'Dashboard 1', where I'd like to show the average 'product_usage' against each percentile category; at the moment I've simply pre-calculated a percentile category against each record so this solution falls apart as soon as I want to filter on any of my dimensions.
I've been scouring the forums for a solution to help me with this issue, some proposed solutions are below:
I've tried both of these but none seem to be helpful for this particular scenario, either because I'm unable to use the percentile function or because I'm trying to calculate the Quantiles at an individual record level.
Any help would be massively appreciated!!