Got it, let's tackle this multi-dimensional NPS filtering issue you're facing. The core problem with your current setup is that you're aggregating too early and only preserving the Segment dimension—plus those S1_Count-style columns are redundant once you have proper dimensional grouping. This locks you out of combining filters like gender, generation, and segment together.

Step 1: Refactor the Base Calculation to Preserve All Dimensions #

First, we need to create a table that retains every critical dimension (Segment, Gender, Generation) alongside the NPS classification for each individual response. This keeps the data granular enough to support any combination of filters later.

CREATE OR REPLACE TABLE Dataset1.NPS_Granular AS
SELECT
  ResponseId,
  Segment_column AS Segment,
  Gender_column AS Gender, -- Replace with your actual gender column name
  Generation_column AS Generation, -- Replace with your actual generation column name
  SAFE_CAST(NPS_Score AS Int64) AS NPS_Score,
  -- Flag each response's NPS category
  IF(NPS_Score > 8, 1, 0) AS Promotor,
  IF(NPS_Score < 7, 1, 0) AS Detractor,
  IF(NPS_Score IN (7,8), 1, 0) AS Neutral
FROM Dataset1.MainTable
WHERE 
  Segment_column IS NOT NULL
  AND Gender_column IS NOT NULL -- Optional: filter out missing gender if needed
  AND Generation_column IS NOT NULL -- Optional: filter out missing generation if needed

Step 2: Use the Granular Table for Flexible Multi-Dimensional NPS #

With this granular table, you can now calculate NPS for any combination of dimensions directly in your dashboard, or pre-aggregate all possible dimension combinations for faster queries:

Option A: Pre-Aggregate All Dimension Combinations (For Larger Datasets)

If you want to precompute totals for every possible segment-gender-generation group to speed up dashboard loads:

CREATE OR REPLACE TABLE Dataset1.NPS_Aggregated AS
SELECT
  Segment,
  Gender,
  Generation,
  SUM(Promotor) AS Total_Promotors,
  SUM(Detractor) AS Total_Detractors,
  COUNT(ResponseId) AS Total_Responses,
  -- Calculate NPS directly here if you want, but keeping components gives more flexibility
  100 * (SUM(Promotor) - SUM(Detractor)) / COUNT(ResponseId) AS NPS_Score
FROM Dataset1.NPS_Granular
GROUP BY Segment, Gender, Generation
-- Add GROUP BY ROLLUP(Segment, Gender, Generation) if you want subtotals for higher levels

Option B: Calculate NPS On-the-Fly in the Dashboard

If your dataset is small (only ~1000 responses), you can skip pre-aggregation and compute NPS directly in your dashboard using the granular table. The formula would be:

100 * (SUM(Promotor) - SUM(Detractor)) / COUNT(ResponseId)

When you apply filters (e.g., Segment = S1, Gender = M, Generation = 千禧一代), the dashboard will automatically compute the NPS for just that subset.

Why This Works #

• The granular table preserves all raw dimensional data, so you can slice and dice by any combination of Segment, Gender, and Generation.
• Ditching the S1_Count-style columns eliminates redundancy—you can get segment counts directly via COUNT(ResponseId) when filtering.
• Pre-aggregating all dimension combinations (Option A) gives you a ready-to-use table for fast dashboard queries, while keeping the granular table (Option B) gives maximum flexibility for ad-hoc analysis.

Example Result for Your Target Filter (S1, Male, 千禧一代) #

If you use the aggregated table, you'd see a row like this (hypothetical numbers):

Or in the dashboard, applying those filters to the granular table would compute 100*(45-12)/80 = 41.25 as the NPS.

内容的提问来源于stack exchange，提问作者ssb223