Approach (short): Missing pivot cells appear NULL because there’s no source row for that combination. Solutions: (A) generate the complete set of combinations (cross-join or dimension table) and LEFT JOIN aggregated facts, then replace NULL with 0; (B) in the pivot/visual layer use null-to-zero functions. Below are examples and BI/Excel tips.SQL — generate all combos + COALESCE (recommended)- Create a matrix of all possible rows × columns (dates × products) then left join aggregated measures and replace NULLs with 0.sql
-- Example: daily sales per product; ensure days with no sales show 0
WITH dates AS (
SELECT CAST('2025-01-01' AS date) AS day UNION ALL SELECT '2025-01-02' UNION ALL SELECT '2025-01-03'
),
products AS (
SELECT 'A' AS product UNION ALL SELECT 'B' UNION ALL SELECT 'C'
),
all_combos AS (
SELECT d.day, p.product
FROM dates d CROSS JOIN products p
),
agg AS (
SELECT CAST(sale_date AS date) AS day, product, SUM(amount) AS total
FROM sales
WHERE sale_date BETWEEN '2025-01-01' AND '2025-01-03'
GROUP BY CAST(sale_date AS date), product
)
SELECT
ac.day,
ac.product,
COALESCE(a.total, 0) AS total
FROM all_combos ac
LEFT JOIN agg a
ON a.day = ac.day AND a.product = ac.product
ORDER BY ac.day, ac.product;
sql
-- conditional aggregation pivot (works cross-platform)
SELECT day,
COALESCE(SUM(CASE WHEN product = 'A' THEN total END),0) AS A,
COALESCE(SUM(CASE WHEN product = 'B' THEN total END),0) AS B,
COALESCE(SUM(CASE WHEN product = 'C' THEN total END),0) AS C
FROM (
-- use all_combos LEFT JOIN agg from previous example as "src"
) src
GROUP BY day
ORDER BY day;
sql
-- assume src has day, product, total (with zeros for missing via left join)
SELECT day, COALESCE([A],0) AS A, COALESCE([B],0) AS B, COALESCE([C],0) AS C
FROM (
SELECT day, product, total FROM src
) src
PIVOT (
SUM(total) FOR product IN ([A],[B],[C])
) p
ORDER BY day;
sql
-- DAX measure
TotalSales := COALESCE(SUM(Sales[Amount]), 0)
sql
TotalSales = COALESCE(SUM(Sales[Amount]), 0)
-- or: IF( ISBLANK( SUM(Sales[Amount]) ), 0, SUM(Sales[Amount]) )
