/* Revenue By State — totals (DatawarehouseNCM) Fact: dbo.FactRevenueByState Revenue rolled up by state, plus the grand total (matches PBI SumXTNDPRCE). */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @State varchar(30) = NULL; -- NULL / '' = All; e.g. 'California' -- By state SELECT f.State, COUNT(*) AS [RowCount], SUM(f.Amount) AS Amount FROM dbo.FactRevenueByState f WHERE f.DocDate BETWEEN @StartDate AND @EndDate AND (@State IS NULL OR @State = '' OR f.State = @State) GROUP BY f.State ORDER BY Amount DESC, f.State; -- Grand total SELECT SUM(f.Amount) AS SumAmount FROM dbo.FactRevenueByState f WHERE f.DocDate BETWEEN @StartDate AND @EndDate AND (@State IS NULL OR @State = '' OR f.State = @State);