sql

[JO8930]

Why the difference?

SELECT SUM([CategorySalesBudget])
FROM [dbo].[SubCategorySalesBudgetPSPD]
WHERE TransDate BETWEEN '2025-04-01' AND '2025-06-30';= 27,945,373,588.89

And this:
SELECT
b.[StoreID],
s.[StoreName],
MONTH([TransDate]) AS [Month],
p.[Section],
p.[Department],
p.[Category],
SUM([CategorySalesBudget]) AS [BudgetSales]
FROM [dbo].[SubCategorySalesBudgetPSPD] b
INNER JOIN ProductDim p ON b.[Subcategory] = p.[Subcategory]
INNER JOIN StoreDim s ON b.[StoreID] = s.[StoreID]
WHERE [TransDate] BETWEEN '2025-04-01' AND '2025-06-30'
GROUP BY b.[StoreID], s.[StoreName], p.[Section], p.[Department], p.[Category], MONTH([TransDate]);

= 4,647,274,218,492.51

[nilendraFabric · Accepted solution]

Hello @JO8930 

The first query simply sums the CategorySalesBudget values directly from the SubCategorySalesBudgetPSPD table with just a date filter

The second query joins the SubCategorySalesBudgetPSPD table with ProductDim and StoreDim tables before summing, which can cause multiplication of values

Check for duplicate keys in your dimension tables and ensure they have unique Subcategory and StoreID values. and Use DISTINCT in your JOIN keys

[v-tsaipranay · Accepted solution]

Hi @JO8930 ,

Thanks for reaching out to the Microsoft Fabric Community. Also thank you @nilendraFabric  for correctly pointing out the cause.

The large difference you're seeing is due to the JOINs in the second query, which can duplicate rows if the dimension tables (ProductDim or StoreDim) contain multiple matching entries for the same Subcategory or StoreID. These duplicates inflate the SUM(CategorySalesBudget).

To resolve this, please verify that the dimension tables contain unique keys for the join fields. If not, you may need to review the data model or apply deduplication before joining.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[nilendraFabric · Accepted solution]

Hello @JO8930 

The first query simply sums the CategorySalesBudget values directly from the SubCategorySalesBudgetPSPD table with just a date filter

The second query joins the SubCategorySalesBudgetPSPD table with ProductDim and StoreDim tables before summing, which can cause multiplication of values

Check for duplicate keys in your dimension tables and ensure they have unique Subcategory and StoreID values. and Use DISTINCT in your JOIN keys