How to Find the Most Popular Product Sub-Category by State in Tableau Using Nested LODs

Table of Contents

1. Key Highlights
2. Introduction
3. Why a straightforward aggregation is insufficient
4. Step 1 — Inner LOD: compute sales for every State + Sub-Category
5. Step 2 — Outer LOD: find the maximum sales per State
6. Step 3 — Identify the winning sub-category per State
7. Build the final bar chart: count states per winning sub-category
8. Handling ties: tie-breaker strategies
9. Null handling and data hygiene
10. Validate your calculations with a text table
11. Alternatives: table calculations and window functions
12. Making the metric selectable: parameterize the measure
13. Use viz-in-tooltip to reveal states behind each bar
14. Performance considerations and best practices
15. Common pitfalls and how to avoid them
16. Real-world example: targeted promotions campaign
17. Extending the technique: time windows and trend analysis
18. Step-by-step checklist to reproduce the workflow in Tableau
19. Troubleshooting tips
20. Common visualization refinements
21. Practical example: sample walkthrough using the Sample Superstore dataset
22. FAQ

Key Highlights

• Use nested FIXED level-of-detail (LOD) calculations to compute per-state, per-sub-category totals, then derive the per-state maximum and identify the top sub-category for each state.
• Convert those identifications into a bar chart that counts the number of states where each sub-category ranks highest; handle ties, nulls, and alternate measures with parameter-driven calculations and viz-in-tooltip enhancements.
• Validate results with a simple text table, consider table-calculation alternatives, and apply best practices for performance and filtering when working with large datasets.

Introduction

Marketing teams, regional managers, and supply-chain planners often need a clear answer to a straightforward question: which product sub-category sells best in each state? Producing that answer requires more than a simple SUM([Sales]) grouped by state and sub-category. The challenge lies in computing two related aggregates at different granularities: the total sales for every state–sub-category pair, and the maximum of those totals within each state. Tableau’s FIXED LODs give precise control over these granularities. Combining an inner LOD to compute per-state-per-sub-category totals with an outer LOD that returns the per-state maximum makes it possible to tag the winning sub-category per state and then count how many states each sub-category wins.

This walkthrough explains each calculation, shows how the pieces fit into a final chart, examines edge cases such as ties and nulls, offers performance and filter-management advice, and outlines practical extensions: making the metric selectable (sales, profit, quantity) through a parameter and embedding a viz-in-tooltip to list or map the states behind each bar.

Why a straightforward aggregation is insufficient

A naive approach might be to place State and Sub-Category on rows and SUM([Sales]) on text, then sort to find the top sub-category per state. That works visually when you inspect each state individually, but it does not produce a single aggregated output that counts the number of states where each sub-category is top. To create that summary you must:

• Compute SUM([Sales]) at the level of State + Sub-Category (not at the level of Sub-Category alone).
• For each State, figure out the maximum of those state–sub-category sums.
• Tag the sub-category or sub-categories that achieve that maximum.
• Aggregate across states to count how many states each sub-category wins.

Tableau’s FIXED LODs are the right tool because they compute an aggregation at the exact granularity you specify, independent of the current view. That independence is powerful but requires careful attention to filtering behavior and to how nested calculations reference one another.

Step 1 — Inner LOD: compute sales for every State + Sub-Category

The first calculation fixes the level of detail to State and Sub-Category and computes the total sales for each pair. This establishes the basis for comparing sub-categories inside each state.

Tableau calculated field: Inner LOD (per State and Sub-Category) {FIXED [State], [Sub-Category] : SUM([Sales])}

Why this works

• FIXED explicitly tells Tableau to ignore the view’s row/column layout and evaluate SUM([Sales]) at the granularity of each state and sub-category pair.
• The result is a single value for every combination of State and Sub-Category; you can display this in a text table to confirm values.

Practical validation

• Build a text table with State, Sub-Category, and the Inner LOD value.
• Sort by State then Inner LOD descending to see which sub-category is largest within each state.

Translation to SQL If you needed the same result in SQL, the inner aggregation resembles: SELECT state, sub_category, SUM(sales) AS sum_sales FROM orders GROUP BY state, sub_category;

This SQL subquery becomes the basis for computing per-state maxima in the next step.

Step 2 — Outer LOD: find the maximum sales per State

Once every state–sub-category sum exists, compute the maximum of those sums within each state. Implement an outer LOD that fixes only on State and returns the highest value from the inner LOD field.

Tableau calculated field: Outer LOD (per State maximum) {FIXED [State] : MAX([Inner LOD])}

Notes and subtleties

• The MAX operates on the Inner LOD values computed for each sub-category within a state.
• The outer FIXED ignores the sub-category dimension, returning the single maximum value per state.
• Because Inner LOD itself is a FIXED expression, Tableau evaluates it independently and the outer FIXED can reference that independent aggregation.

SQL equivalent Using the SQL subquery from the previous section: SELECT s.state, MAX(s.sum_sales) AS max_sum_sales FROM ( SELECT state, sub_category, SUM(sales) AS sum_sales FROM orders GROUP BY state, sub_category ) s GROUP BY s.state;

This yields the highest sub-category sales total for each state.

Step 3 — Identify the winning sub-category per State

With both per-pair sums (Inner LOD) and per-state maximums (Outer LOD), identify the sub-category or sub-categories that match the maximum in their state.

Tableau calculated field: Max Sub-Category (tag the winner) IF [Inner LOD] = [Outer LOD] THEN [Sub-Category] END

Behavior of this calculation

• For a state where only one sub-category reaches the state's maximum, this returns that sub-category.
• For states where two or more sub-categories tie for the highest sales, the calculation returns each tied sub-category (one result per tied pair).
• For sub-category/state combinations that are not maximal, the calculation returns NULL.

Practical step

• Build a simple text table with State, Sub-Category, Inner LOD, Outer LOD, and Max Sub-Category to confirm behavior.
• You should see for each state the sub-category rows flagged (Max Sub-Category filled) where Inner LOD equals Outer LOD.

Ties: expected behavior and decision points

• A tie will produce multiple sub-categories marked as winners in the same state. Whether you want each tied sub-category to "win" the state depends on your business rules.
• If a strict single winner per state is required, you must break ties by an additional rule (alphabetical, by profit, by quantity, by store count, or arbitrary but repeatable). Later sections show how to handle tie-breaking.

Build the final bar chart: count states per winning sub-category

The goal is a bar chart where each bar represents a sub-category and the bar’s length equals the number of states where that sub-category had the highest sales.

Steps in Tableau

1. Place [Max Sub-Category] on Rows.
2. Create a measure: COUNTD([State]). Put this on Columns.
   • You can use COUNTD on the State field because Max Sub-Category returns a sub-category only for the state–sub-category rows that won. Counting distinct State ensures each state is counted once per sub-category.
3. Exclude NULL values from Max Sub-Category (filter out Null) so that only winning records are counted.
4. Sort the bars in descending order to present the most-winning sub-categories first.

Interpretation of the result

• Each bar shows how many distinct states were "won" by that sub-category.
• Example outcome (from the source exercise): Phones was the top sub-category by sales in 13 states.

How the count behaves with ties

• If a state ties between two sub-categories, both sub-categories will show that state in their COUNTD([State]) totals. The state counts toward both bars.
• If you must avoid double-counting tied states (assign each state to a single sub-category), implement a tie-breaker before creating the bar chart.

Handling ties: tie-breaker strategies

Ties are common in coarse data or where categories have similar sales. Select a tie-breaking method that aligns with business intent. Below are practical options with Tableau implementations.

1. Alphabetical tie-breaker (deterministic)

• Create a calculated field that returns the smallest (alphabetically) sub-category among the tied winners.
• Implementation using an outer FIXED again: {FIXED [State] : MIN(IF [Inner LOD] = [Outer LOD] THEN [Sub-Category] END)}
• MIN on strings returns the alphabetically earliest value. This approach is deterministic but arbitrary from a business standpoint.

2. Break ties by another metric (profit, quantity)

• Decide a secondary metric and choose the sub-category among ties with the highest value of that metric.
• Implementation:
  • Build an Inner LOD for the secondary metric, e.g., {FIXED [State],[Sub-Category] : SUM([Profit])}
  • Then compute: {FIXED [State] : MIN(IF [Inner LOD]=[Outer LOD] THEN [Sub-Category] END)} but MIN of sub-category weighted by the secondary metric is not directly possible. Instead, derive the winner with a nested approach:
    • Create a boolean [IsTopBySales] = [Inner LOD] = [Outer LOD]
    • Create a per-state tie-breaker: {FIXED [State] : MAX(IF [IsTopBySales] THEN [Profit Inner LOD] END)}
    • Then select the sub-category where [IsTopBySales] AND [Profit Inner LOD] = [TieBreaker Value].

3. Use a RANK and table calculation (unique winner)

• Compute a rank partitioned by State ordered by SUM([Sales]) descending; choose rank = 1.
• Caveat: table-calculation ranks depend on view layout and require the Sub-Category and State dimensions present in the view and configured partitioning. This shifts logic from data model LOD to visualization layer and may be harder to reuse in other sheets.

4. Use business rules (store count, recency)

• Tie-break on number of stores, most recent date of sale, or another operational rule.
• Implement via additional LODs similar to the profit tie-breaker.

Choosing a method

• If repeatability and explainability are important, prefer an explicit tie-breaker (profit or quantity) over alphabetic order.
• Document the rule in the dashboard tooltip or a legend so consumers understand how ties are resolved.

Null handling and data hygiene

Null values appear when Max Sub-Category returns no value for a given state–sub-category row (i.e., not a winner). Filter out NULLs in the Max Sub-Category calculation before counting states. Steps:

• Add [Max Sub-Category] to Filters and exclude Null.
• Alternatively, create a boolean [IsStateWinner] = NOT ISNULL([Max Sub-Category]) and filter on TRUE.

Check data completeness

• Nulls may also indicate missing State values in source data. Verify that every transaction has a valid State entry or handle missing geography using a placeholder (e.g., "Unknown").

Validate your calculations with a text table

Validation reduces errors and increases confidence that the final chart reflects intended logic.

Useful validation table

• Columns: State, Sub-Category, Inner LOD, Outer LOD, IsTop, Max Sub-Category
• Sort by State then Inner LOD descending.
• Confirm that for each state:
  • Outer LOD equals the largest Inner LOD value.
  • Max Sub-Category returns the correct sub-category name for the winning row(s).
  • Tied rows are clearly visible and handled per your chosen tie rule.

Cross-check with raw-level aggregations

• Export an aggregated CSV from Tableau or run equivalent SQL to cross-validate results.
• SQL validation approach:
  • Build the per-state, per-sub-category totals as a subquery.
  • Join that result to a per-state max subquery.
  • Filter where sums equal max to list winners per state.

Alternatives: table calculations and window functions

Two common alternatives to nested FIXED LOD calculations are table calculations (e.g., RANK) and SQL-level window functions. Each has trade-offs.

Table calculations (RANK, INDEX)

• Advantages: Can produce unique winners via RANK_UNIQUE; flexible formatting; no need for nested LODs.
• Disadvantages: Depend on the view partitioning and sort order. If you want a single reusable calculated field across multiple sheets or dashboards, table calculations are less portable. They also compute after dimension filters (in contrast to FIXED LOD behavior), which can be desirable or problematic depending on your filter strategy.

Window functions (SQL)

• Advantages: Push computation to the database for performance and leverage robust SQL constructs (ROW_NUMBER() OVER (PARTITION BY state ORDER BY sum_sales DESC)).
• Disadvantages: Requires direct access to and ability to modify the data source (custom SQL or view). Not always available when using published extracts or governed data sources.

Comparing behavior with filters

• FIXED LODs ignore dimension filters unless you add those filters to context. Table calculations operate after filters. Choose the approach that matches your intended filter semantics.

Example SQL using window functions WITH sub_totals AS ( SELECT state, sub_category, SUM(sales) AS sum_sales FROM orders GROUP BY state, sub_category ), ranked AS ( SELECT state, sub_category, sum_sales, DENSE_RANK() OVER (PARTITION BY state ORDER BY sum_sales DESC) AS rnk FROM sub_totals ) SELECT sub_category, COUNT(state) AS states_won FROM ranked WHERE rnk = 1 GROUP BY sub_category ORDER BY states_won DESC;

This returns state counts per winning sub-category, handling ties with DENSE_RANK (ties get same rank).

Making the metric selectable: parameterize the measure

Business users often want to see winners by Sales, by Profit, or by Quantity. Use a parameter to select the measure, and make LODs use a measure field that references that parameter.

Step-by-step

1. Create a parameter [Metric Selector] with allowed values such as "Sales", "Profit", "Quantity".
2. Create a calculated field [Selected Measure] that chooses the underlying field: CASE [Metric Selector] WHEN 'Sales' THEN [Sales] WHEN 'Profit' THEN [Profit] WHEN 'Quantity' THEN [Quantity] END
3. Use [Selected Measure] inside your Inner LOD: {FIXED [State], [Sub-Category] : SUM([Selected Measure])}
4. Outer LOD remains: {FIXED [State] : MAX([Inner LOD])}
5. The Max Sub-Category calculation is unchanged but now reflects the selected measure.

Parameter action enhancement

• Replace the parameter control with a parameter action (click in another sheet to change the parameter) for interactive dashboards.
• This allows users to click a chart or button and instantly switch the metric used to determine winners.

Practical notes

• Make sure [Selected Measure] returns a numeric type. Using a parameter-driven field keeps the LOD expressions stable and avoids having to write separate sets of nested LODs for each measure.

Use viz-in-tooltip to reveal states behind each bar

Rather than listing states in a separate table, embed a small sheet in the bar chart’s tooltip showing the state list or a small map of states that a sub-category wins.

How to implement

1. Create a sheet (e.g., "States per Sub-Category") that contains State and maybe a mini bar or map, filtered to show only the states where Max Sub-Category equals the parameter or the sheet’s sub-category context.
2. Format that sheet compactly — remove headers, reduce fonts, and limit rows to make it suitable for a tooltip.
3. On the main bar chart, edit Tooltip and insert a viz-in-tooltip:
   • Select "Insert" > "Sheets" and choose the mini-sheet.
   • Configure to pass the Sub-Category value from the bar chart into the tooltip sheet so it filters appropriately (use the Sub-Category field in the tooltip placeholder).
4. When users hover over a sub-category bar, the tooltip shows the embedded viz listing the states that contributed wins to that sub-category.

Key benefits

• Provides context without cluttering the dashboard.
• Lets users inspect the distribution of winning states per sub-category interactively.

Limitations

• Viz-in-tooltip can be slower on large datasets or with heavy calculations inside the embedded sheet; minimize complexity inside the tooltip sheet.

Performance considerations and best practices

Nested LODs are convenient and expressive, but on large datasets they can have performance impact if used indiscriminately.

Best practices

• Use extracts for large datasets, and test performance on a representative sample before publishing.
• Push heavy aggregations to the data source where possible (SQL view, materialized table).
• Reduce the cardinality of fields in LODs where possible (avoid unnecessary dimensions).
• Limit the scope of the viz-in-tooltip to a small, optimized sheet.

Filter semantics and context filters

• Remember that FIXED LODs compute before dimension filters but after data source and context filters. If a filter should affect FIXED LODs, add it to Context. Otherwise, FIXED will ignore it.
• Example: If you filter the view to one year by dragging [Order Date] YEAR to Filters, that filter will not change fixed LODs unless you place it in Context. Use context filters deliberately.

Avoid repeated expensive nested LODs

• If you need similar nested LOD logic in multiple sheets, compute intermediate LODs once as calculated fields in the data source or publish a calculated field in the data source layer for reuse.

Testing for correctness and speed

• Create test sheets that only use the LOD fields and measure compute time.
• Use Tableau's Performance Recording (Help > Settings and Performance > Start Performance Recording) to find slow queries and optimize.

Common pitfalls and how to avoid them

1. Expecting FIXED LODs to obey all filters
   • Clarify which filters should apply. If a dimension filter (e.g., Category) must influence the LOD, add it to Context or use a different approach.
2. Referencing aggregate fields incorrectly
   • Avoid placing aggregated expressions inside aggregations. Compute the base aggregation in an LOD and then reference it.
3. Counting states incorrectly
   • Use COUNTD([State]) to avoid counting multiple records for the same state within a sub-category, especially when tying or multi-row detail exists.
4. Unintended double counting due to ties
   • Decide whether tied states should count for all tied sub-categories or be assigned to one. Implement tie-breaking accordingly.
5. Wrong partitioning with table calculations
   • If you opt for RANK table calculations, ensure table calculations are configured with correct partitioning (Compute Using) and that the view contains the required dimensions.
6. Viz-in-tooltip causing slowdowns
   • Simplify the embedded sheet and limit the number of rows displayed.

Real-world example: targeted promotions campaign

A retail chain plans a regional promotion budget allocation based on the number of states where each product sub-category leads sales. Steps:

• Use the nested LOD method to find the winning sub-category per state.
• Count how many states each sub-category wins.
• Present the bar chart to marketing leadership. Top sub-categories with the most state wins receive higher promotion budgets.
• Supplement the bar chart with a viz-in-tooltip that lists states per sub-category so regional managers can quickly see whether their region is included.
• Use parameter-driven measure selection to allow leaders to test alternate allocation strategies (profit-based vs. sales-based wins).

This approach ensures promotional spend targets sub-categories that not only have strong totals but are geographically widespread winners.

Extending the technique: time windows and trend analysis

The same nested LOD approach adapts to time-based questions: which sub-category dominates each state in 2022? Which sub-category had the most top-state wins month-over-month?

Per-year winners

• Add [Year] into the LOD calculations to produce per-state-per-year-per-sub-category totals: {FIXED [State], [Sub-Category], [Year] : SUM([Sales])}
• Then find per-state-per-year maxima: {FIXED [State], [Year] : MAX([Inner LOD])}
• Identify winners: IF [Inner LOD] = [Outer LOD] THEN [Sub-Category] END
• Aggregate counts by Year and Sub-Category to produce time-series bars or small multiples.

Trend analysis

• Compute counts per month/year and plot lines per sub-category showing the number of states won over time. This reveals whether a sub-category’s regional dominance is expanding or contracting.

Rolling windows

• Apply DATEADD/DATEDIFF logic or use context filters to limit LODs to rolling 12-month windows. For example, create a boolean [InRollingWindow] that tags rows within the last 12 months relative to a parameter date, then add that filter to Context so the FIXED LOD respects the rolling period.

Step-by-step checklist to reproduce the workflow in Tableau

1. Create [Inner LOD] = {FIXED [State],[Sub-Category] : SUM([Sales])}.
2. Create [Outer LOD] = {FIXED [State] : MAX([Inner LOD])}.
3. Create [Max Sub-Category] = IF [Inner LOD] = [Outer LOD] THEN [Sub-Category] END.
4. Build a text table: Rows = State, Sub-Category; Columns = [Inner LOD], [Outer LOD], [Max Sub-Category]; validate.
5. Build the final bar chart:
   • Rows: [Max Sub-Category]
   • Columns: COUNTD([State])
   • Filter: Exclude Null on [Max Sub-Category]
   • Sort: Descending
6. If needed, implement tie-breaker logic before counting.
7. Add a parameter [Metric Selector] and [Selected Measure]; replace SUM([Sales]) inside [Inner LOD] with SUM([Selected Measure]).
8. Create a small sheet for viz-in-tooltip showing state list or map. Embed via Tooltip.
9. Test filters—determine which filters should be Context to affect FIXED LODs.
10. Optimize with extracts or push computations to the database if performance suffers.

Troubleshooting tips

• If Outer LOD does not equal the highest Inner LOD within a state:
  • Verify that Inner LOD is computed as shown and that the field referenced in Outer LOD is the exact Inner LOD field (no accidental redefinition).
  • Check for data source filters or context that might alter the source numbers.
• If COUNTD([State]) returns unexpected numbers:
  • Check whether sub-categories have multiple rows per state after the Max Sub-Category logic. COUNTD avoids counting duplicates, but if a state is associated with multiple winning sub-categories (ties), it will be counted for each winning sub-category.
• If tooltip viz is blank:
  • Confirm the worksheet used for the tooltip is receiving the expected Sub-Category filter from the main chart. Ensure fields used for linking are present and spelled exactly the same.
• If the dashboard is slow:
  • Use Performance Recording to identify bottlenecks.
  • Simplify the viz-in-tooltip sheet or remove heavy LODs from sheets that appear frequently.

Common visualization refinements

• Display the number of states on the bar label for quick scanning.
• Add a map button or small map in the tooltip to show geographic distribution of wins.
• Provide a toggle (parameter or button) to include/exclude ties from the count.
• Use color to group sub-categories into broader categories (Category) to show whether top states are concentrated in a few major categories.

Practical example: sample walkthrough using the Sample Superstore dataset

Assume Sample Superstore data with standard fields [State], [Sub-Category], [Sales], [Profit].

1. Inner LOD: {FIXED [State], [Sub-Category] : SUM([Sales])}
2. Outer LOD: {FIXED [State] : MAX([Inner LOD])}
3. Max Sub-Category: IF [Inner LOD] = [Outer LOD] THEN [Sub-Category] END
4. Final Bar: Rows = [Max Sub-Category], Columns = COUNTD([State]), filter out Nulls.

Expected observation

• The bar chart reveals the number of states each sub-category leads. In one published example, Phones lead in 13 states.

Interpretation and action

• A regional product manager sees Phones leading in many states and may prioritize phone-centric promotions in those regions, while other regions might require a different strategy.

FAQ

Q: How do FIXED LODs interact with filters? A: FIXED LOD calculations ignore dimension filters unless those filters have been added to Context. Data source filters and extract filters do affect FIXED LODs. Add a dimension filter to Context when you require it to change the LOD result.

Q: What happens if two sub-categories tie for top sales in a state? A: The simple Max Sub-Category calculation returns all tied sub-categories for that state. If you count states per sub-category, that state will count toward each of the tied sub-categories. To enforce a single winner per state, implement a tie-breaker (alphabetical, secondary metric such as profit, or a business rule).

Q: Can I use Profit or Quantity instead of Sales? A: Yes. Create a parameter for the metric and a calculated field that returns the selected underlying field. Use that calculated field inside the Inner LOD: {FIXED [State],[Sub-Category]: SUM([Selected Measure])}. Everything downstream will reflect the chosen metric.

Q: Could this be done with table calculations instead? A: Yes. Table calculations like RANK_UNIQUE or DENSE_RANK partitioned by State and ordered by SUM([Sales]) descend can identify top sub-categories. Table calculations require careful handling of partitioning and compute-using directions and operate after view-level filters. Use them if you prefer view-level ranking and have consistent views.

Q: How do I avoid counting a state twice if it ties? A: Decide on a tie-breaker and implement it before the COUNTD([State]) aggregation. For instance, select the tied sub-category with higher profit or apply an alphabetic rule. Alternatively, subtract ties from counts or report ties in a separate metric.

Q: Are there performance concerns with nested LODs? A: Nested LODs can be costly on very large datasets. Use extracts, push calculations to the data source, minimize cardinality in FIXED expressions, and profile your workbook using Tableau’s Performance Recording. Simplify viz-in-tooltip sheets and consider SQL-level window functions for heavy workloads.

Q: How can I show the states that contribute to each bar without adding another sheet to the dashboard? A: Use viz-in-tooltip. Create a compact sheet that lists states (or a small map) and embed it in the bar chart’s tooltip. Configure the tooltip to pass the Sub-Category value so the embedded sheet filters to show only the states for that sub-category.

Q: Are these LODs affected by row-level security or published data source settings? A: Yes. Data source filters and any server-level row-level security will apply to LODs. Ensure your security model and filters are properly configured to produce expected LOD results.

Q: Can I export the state winners to share with other teams? A: Yes. Build a view that lists State and its winning Sub-Category, then export that view as CSV or publish it to Tableau Server so downstream users can access the results. Alternatively, run the equivalent SQL and export the results from the database.

Q: How do I include time-series analysis (e.g., top sub-category per state per year)? A: Include the time dimension in the LODs. For yearly winners, compute {FIXED [State],[Sub-Category],[Year] : SUM([Sales])} and {FIXED [State],[Year] : MAX([Inner LOD])}, then compare as before. Use Year on columns or create a small-multiples layout to visualize changes across time.

Q: What is the difference between FIXED and INCLUDE/EXCLUDE LODs for this task? A: FIXED explicitly sets the granularity and ignores the view’s dimension composition. INCLUDE and EXCLUDE adjust the granularity relative to the view. For per-state-per-sub-category totals independent of the view, FIXED is the most straightforward. INCLUDE could be used when you want the LOD to be responsive to dimensions already in the view.

Q: If I change the dashboard filter for Category, will my LOD results update? A: Not unless the Category filter is added to Context. FIXED LODs are computed before regular dimension filters are applied. Add category filters to Context to make them affect FIXED LOD computations.

Q: How do I handle inconsistent state naming or missing states? A: Clean data at source or add data-preparation steps in Tableau Prep or a derived SQL view. Standardize state names and ensure no transactions have null State values. Replace missing states with a placeholder if analysis requires it.

Q: Where should I document the logic and tie-breaking rules for end users? A: Include a short note on the dashboard — in a caption, tooltip, or an info icon — describing how winners are determined, how ties are handled, and which filters affect the computation. Transparency prevents misinterpretation.

This method — inner FIXED LOD for per-state-per-sub-category totals, outer FIXED LOD for per-state maxima, and a comparison to tag winners — yields a robust, repeatable way to summarize geographic dominance by sub-category. It integrates well with parameter-driven selection of measures and interactive enhancements like viz-in-tooltip, while offering clear options to resolve ties, handle filters, and scale to larger datasets.