I have a dashboard based on multiple published Cloud data sources -
Primary= Agent Daily Structure
Blended tomultiple KPI data sources on staff number and date only
The end user wants to see multiple KPIs on a dashboard page showing the grand total and then splitting the agents into quartiles (just the 4 totals showing). They then want to be able to use filters to change the month or drill down/by department/manager etc to which the quartiles will re-adjust.
I tried building my initial data source in Prep, as all of the sources are already published to Cloud, with left joins on staff number and date to the primary data source and cleaning up, rather than the 'blends' but the agent-level totals for the KPIs did not reconcile against the data source in isolation, before even trying quartile calculations, hence trying it the 'blended way'. This is giving correct agent and grand totals.
I have tried various ChatGPT suggestions but none of them work (sometimes its a great AI tool, but in this case no!). I've not been able to find anything in forum posts/guides that seems to suit my scenario. Can you help?
This one 90% works using a DATETRUNC to 'month-date' blend on each data source, rather than individual date - QA score
Fixed LOD Calc 1
=
{ FIXED [Agent ID] , [DateTrunc - Month]: (AVG([Evaluation Score])/100) }
Creating the Quartile groups
=
IF [Fixed LOD - QA] <= { PERCENTILE([Fixed LOD - QA], 0.25) } THEN "Q4 - Bottom Performers"
ELSEIF [Fixed LOD - QA] <= { PERCENTILE([Fixed LOD - QA], 0.50) } THEN "Q3 - Coaching Needs"
ELSEIF [Fixed LOD - QA] <= { PERCENTILE([Fixed LOD - QA], 0.75) } THEN "Q2 - Good Performers"
ELSEIF [Fixed LOD - QA] > { PERCENTILE([Fixed LOD - QA], 0.75) } THEN "Q1 - Top Performers"
END
Quartile groups on rows, QA score on column and the text mark to display in columns. This works 90% for QA score (the totals for each Q at least look reasonable and adjust when filtering) - the odd agent appears under the wrong quartile when adding agents to the view to check the quartile calc/sorting highest to lowest. But if I try the same calc on another Quality metric in the same data source it doesn't work at all - its grouped everyone under the bottom 2 quartiles.
I've tried multiple ChatGPT suggestions using Windows_Percentile and Rank_Percentile and using the staff number or staff number and date on the 'detail mark' and keeping the first row, but none of these work.
I need a reliable way to split agent performance on a KPI into quartiles using this blended data source, that can be filtered. Surely something like it exists/its not an impossible ask!! TIA
#Tableau Desktop & Web Authoring #Tableau Cloud #LOD
Your quartile logic itself looks fine. I tested the same pattern (FIXED [Agent ID] -> FIXED PERCENTILE -> IF ... <= cut-off) in a single data source with sample data, and it splits 120 agents evenly into four groups of 30. So I would look at these two things:
1. The metric that "groups everyone under the bottom 2 quartiles" probably has many tied values. If more than 25% of agents share the same value (e.g. 100%), the 25/50/75% cut-offs become identical and "<=" puts all of them in the lower groups. In my test, 60% of the agents scored 100: 90 agents landed in Q3, 30 in Q4, and none in Q1/Q2. No value-based split can separate tied agents, so use a tie-breaker (e.g. a second metric added as a tiny weight) or treat that value as its own group.
2. The quartiles not re-adjusting properly is likely related to the blend. FIXED LODs ignore dimension filters unless the filters are context filters, and LODs on secondary-source fields are computed within the secondary source. I would combine the data in one source (in Prep, aggregate each KPI source to one row per staff number + date before joining, to avoid duplicated rows) and then right-click your Month / Department / Manager filters and choose "Add to Context".
I attached a small workbook with synthetic data: sheet 1 is your calculation in a single data source, sheet 2 shows the tie case.