sum does not equal total

[jworthulf]

I have two fields in a table and I have made selections in field one (text) values that have associated values in field 2.     When I go, to sum up the values of the fields that I've selected using sum(field) and my total is incorrect.  

Example

fields selected in a list box are B,C,D

Table 1

                    sum(Field2)

Field 1          Field 2

B                    5

C                    4    

D                    3

                      6 = Total

[jworthulf]

William,

Thank you for the feedback, the total appears of 39 appears to be wrong as all the values under field 3 are selected (green) and if you manually add up the numbers 52 appears to be correct. The only other thing I will add is the sort order of field 3 in the table was different vs the list box for field 3, I did not show this in the example.

Is there anything that could be going on with the values under F or a combination of B&E or A&E as the value difference is 13?

Thanks,

John Worth

BI Data Analyst

Upper Lakes Foods

p: 800.879.1265 ext 4278

w: upperlakesfoods.com<http://upperlakesfoods.com/>

[sagarjagga]

Yes John,

Are you using distinct function in your set analysis(to find out sum)?

[swuehl · Accepted solution]

You are probably experiencing an issue caused by the difference between a sum-of-rows and expression total evaluation of your expression in the total line.

This may rise up e.g. with a little more complex data model:

Facts:

LOAD * INLINE [

    Dim, Key

    A, 1

    A, 2

    B, 1

    C, 2

    D, 3

    E, 1

    E, 2

    E, 3

    F, 4

];

Numbers:

LOAD * INLINE [

Key, Number

1, 1

2, 2

3, 3

4, 4

];

Then creating a straight table with dimension Dim and two expressions (both = Sum(Number) but with different total mode):

Dim	sum of rows	expression total
B	1	1
C	2	2
A	3	3
D	3	3
F	4	4
E	6	6
	19	10

[jworthulf]

Stefan,

Can you walk me thru the expression total and show me how the system went about getting 10.

Thanks,

John

[vishsaggi]

What stefan pointing to was Total Mode in expression tab like below:

Here the first expr total mode is Sum of rows. So it will sum the rows as is.

The 2nd expr Total mode is Expr Total. So here it is taking the sum of Distinct Number value and then it sums up. Hence 10 in total.

[swuehl]

It's in detail explained in

Totals in Charts

[jworthulf]

Vishwarath,

I’m assuming the total expression was summing just the totals and the distinct values are actually totals

Expression Total:

A B C D E F

Total 1 2 3 4 = 10

1 1

2 2

3

Sum of Rows:

A B C D E F

Total 3 1 2 3 6 4 =19

1 1

2 2

3

Thanks

John

[vishsaggi]

Yes.

[jworthulf]

Vishwarath,

Thank you for letting me know this as I've only placed a couple posts.

Also thank you for all your help.

John Worth

BI Data Analyst

Upper Lakes Foods

p: 800.879.1265 ext 4278

w: upperlakesfoods.com<http://upperlakesfoods.com/>

[jworthulf]

Stefan,

Thanks for the reminder I really appreciate all the hlep.

Have a great weekend.

John Worth

BI Data Analyst

Upper Lakes Foods

p: 800.879.1265 ext 4278

w: upperlakesfoods.com<http://upperlakesfoods.com/>