SUM() function adds the values not once, but twice in a given year

[MartijmH]

Hello everybody,

I have a really easy SUM() measure, but the outcome for the year 2022 is twice the value of the column value..

Below you can find the fact table I'm using. The columns [Date], [Store] and [Sales] are from the data itself. The column [Sum of Sales] is my measure: Sum of Sales = SUM('Table'[Sales]). The measure must show exactly the values of the column [Sales] as it is a simple SUM(). This is the case for 2021 and 2023, but not for 2022.. Here the outcome is twice the value of the column [Sales]. I have never seen anything like this and it is really weird that it only happens for the year 2022.

Date		Store		Sales		Sum of Sales
1-1-2023 00:00		Store A		1424		1424
1-12-2022 00:00		Store A		1030		2060
1-11-2022 00:00		Store A		802		1604
1-10-2022 00:00		Store A		680		1360
1-9-2022 00:00		Store A		1040		2080
1-8-2022 00:00		Store A		750		1500
1-7-2022 00:00		Store A		1270		2540
1-6-2022 00:00		Store A		930		1860
1-5-2022 00:00		Store A		1030		2060
1-4-2022 00:00		Store A		806		1612
1-3-2022 00:00		Store A		1783		3566
1-2-2022 00:00		Store A		802		1604
1-1-2022 00:00		Store A		1008		2016
1-12-2021 00:00		Store A		607		607
1-11-2021 00:00		Store A		403		403
1-10-2021 00:00		Store A		1130		1130
1-9-2021 00:00		Store A		1240		1240
1-8-2021 00:00		Store A		980		980
1-7-2021 00:00		Store A		1170		1170
1-6-2021 00:00		Store A		1400		1400
1-5-2021 00:00		Store A		950		950
1-4-2021 00:00		Store A		1040		1040
1-3-2021 00:00		Store A		300		300

Some extra information: only two dimension tables [Calendar] and [Stores] are linked with a one-to-many relationship to the fact [Table], but I don't even use columns from these tables in the above table.

Has anyone ever experienced this? I would like to know what the problem is and what the solution is. Thank you in advance.

Martijm

[FreemanZ]

hi @MartijmH 

The issue is not on year, but you are experiencing unexpected context transition.

Sum of Sales is a measure, so it is actually runned as
CALCULATE(SUM('Table'[Sales]))
CALCULATE convert date, store and sales values on every current row into filter contexts, only those identical to the current row will be summed.

For example,
1-12-2022, Store A, 1030
1-11-2022, Store A, 802
have two identical rows, so two identical Sales are summed, so it is doubled.

but
1-1-2023, Store A, 1424
has no identical rows, so it only sum the Sales itself on the current row. 

Actually, it is not suggested or at least one needs to be very care using CALCULATE or a measure (surrounded with a implicit CALCULATE) in creating calculated column.

For more information about context transition, check:
https://www.sqlbi.com/articles/understanding-context-transition/#:~:text=Context%20transition%20is%2....

[tamerj1]

@MartijmH 

The only chance is having duplicate rows for that year. I cannot think of anything else. Triple check 

[Greg_Deckler]

@MartijmH Unable to replicate this. See attached PBIX below signature. My only guess is that for some reason the 2022 data is actually duplicated in the table and that the Sales column is using a implicit aggregation of something like Min or Max or even Average. Hard to say without more information or the actual PBIX.