I have a value which is supposed to SUM room minutes, however I cannot get the value to line up with the manual calculation.
Here is my table model:
Here are the individual values for the room minutes:
The sum of this is 1880
Here is what I get when using the SUM function:
The value represented from this formula is 1970...?
Why is the SUM value incorrect?
What you are seeing is the distinct values within Room Minutes field, but there might be multiple values for the same number. Sum([Room Minutes]) will sum all values. Not a good idea, but Sum(DISTINCT [Room Minutes]) will give the sum of distinct values within Room Minutes field. Is that what you want?
May be use this in text box and then see in Straight/Pivot
Sum(TOTAL [Room Minutes])
Sum(Distinct [Room Minutes])
Add Dimensions to the straight table from the tables CALBLOCK and STGII, you will be able to see where the data is duplicating. If still cannot find the values on which the expression is duplicating try adding dimensions from CASES table as well.
It seems to me like your issue can be explained only looking at the CASES table, since both the dimension and expression field originate from that table.
What is the primary key of that table? Is it [Case No], i.e. is there only a single record per case no?
Or is it [Log ID]? Or a combination of fields?
Try to add the primary key to your straight table as dimensions.
Another way to investigate could be to check your data source and script. Are you using JOIN in your script related to table CASES? A JOIN may unintentionally duplicate records.
After all, maybe the value shown is indeed correct, so we might need also to discuss the way you've calculated the sum manually. How have you done it? As Sunny pointed out, a list box or table box will only show distinct combinations of values, it will not show duplicate records or sets of identical values.