I have an straight table in which I have two two expressions for which i am showing the Total value on the top row.I see the Total as 775.5$ but when I export the excel and sum up the values I get 765.4$.. What could be the reason for this??
"The difference in sum of rows and expression typically happens when you have something like a ratio (as in your example), that is not additive. For example, consider two data points: 1/3 and 2/3
When added together you will get 1/2+2/3 = 1, but when calculated with the expression, you will get (1+2)/(3+3) = 1/2. The expression is mathematically "correct", and is not arithmetically the same as the sum of rows."
and you create a straight table chart with dimension Dim and expression sum(TestValue), you'll see the difference.
The expression total will sum up the TestValues in global context, like in a text object, without using the dimension to group the results by. So it's just looking at the second table, and sum TestValue over the three records, this will return 6.
In your table rows, the sums are to be calculated in the context of the dimension, so we need to follow Link to get the TestValues per Dim. This will give us 3 and 5 for the dimension values A and B. The sum of rows is 8.
You can get a similar issue also with a single table
LOAD * INLINE [
Dim, TestValue, TestValue2
A, 1, 2
B, 2, 2
C, 3, 2
];
and calculating as expression sum(TestValue) / sum(TestValue2)
The expression total will return 1 (six divided by six), while the sum of rows is 0.5 + 1 + 1.5 = 2.
The expression total will not sum up the single rows, it will evaluate the expression in total or global context (no dimensions taken into account). If you need a sum of the rows, you'll need to change the total mode (for a pivot table, you can't change the total mode in the expression tab, but there is a workaround using advanced aggregation, look into using sum-of-rows in pivot tables using advanced aggregation in the Help).
If I put the same expression in text object it gives me 775.5 which is corect.. but why does that give 765.5 when i use the same in the straight table.. actually the data model is bit tough and due to security issues i could nt post d example
>...but why does that give 765.5 when i use the same in the straight table.
You mean 765.5 as sum of rows in Excel? or do you see the value 765.5 now also in the straight table? I must admit I am a bit confused.
Could you post the dimensions / expression you are using in the straight table? Are you using alternate states / dimension limits or some other non-default setting?
Could you at least try to explain your data model, as far as relevant to your chart object?
It is really difficult to explain why you see some differences between Excel and your chart object with this little details given.
I see 765.5 as total when i export the data to excel and sum up the values.. but when i select the expression totla in the chart it shows me 775.5 as total at the same tome when i chnage the expression total to sum of rows it gives me 765.5. I am attaching the excel in which you could see the total mismatch.Please let me know if you need any info.. In the current estimate column i use set analysis in order to create it..
Thank u so much for spending your time in order to help me with this issue..
Ok, so you do see a difference between total modes sum-of-rows and expression total. And sum in excel equals the sum-of-rows. Is this correct?
If so, I would try focusing on the difference between sum-of-rows and expression total, and not looking at excel files, since this is just adding more confusion.
I assume that your issue is due to your data model (in combination with the expression used), so without knowing any more details, I can't help you here.
