I work for a survey research company in the healthcare industry. We are looking into the possibility of using Tableau for some interactive reporting, but have recently come across a data blending problem in Tableau that I am hoping maybe someone here might be able to help us with.
Is there any workaround that allows you to blend on an aggregated (averaged) measure?
I am trying to figure out a way to display a healthcare organization's percentile ranks with respect to a particular peer group. For example, if respondents rate an urgent care center on average 3.14 out of 5 for a particular survey question, that might put them in the 17th percentile of all urgent care centers in our database. Calculating these percentile ranks on the fly is not an option due to the volume of data required. Instead what we typically use for this are lookup tables - our SQL queries can calculate the mean score for the survey question, then join to a percentile table containing the cutoffs by percentile rank for the chosen peer group, imposing the constraint that the mean score must fall between our cutoff values. For my use case the lookup tables are not stored in the same database as the survey response data, so in Tableau blending (as opposed to joining) is probably the right approach. In order to avoid blending with inequality constraints (which I don't think is possible in Tableau?), we can restructure the lookup tables so that they are keyed on the mean score as opposed to the percentile rank. However, the problem remains that blending is restricted to dimensions only, and not aggregated measures.
I have attached a packaged workbook that showcases my problem. The workbook contains two data sources - Data, which has some sample data for a survey question, and PRanks, which is the lookup table, keyed on mean score rounded to three decimals. I would like to blend the two data sources (linking on some variant of AVG([Response on Q1]) = [Mean Score]) to display the percentile rank for organization A on question 1. Is that possible?
Demo.twbx 68.4 KB