Incorrect totals in a Pivot Table

[#1]

I have a pivot table with 3 dimensions:

Critical Crashes			2007 Q1	2007 Q2	2007 Q3	2007 Q4
Loss Of Control	0-100K		952	401	393	665
Loss Of Control	100K-250K		7	5	7	7
Loss Of Control	250K-500K		2	2	0	2
Loss Of Control	500K+		3	3	4	3
Loss Of Control	Total		953	404	397	672

As you can see, the partial sum totals are incorrect.  The expression is a simple one:

sum

({$<[Claim Company] = {'XXXX'}>}[Accident Counter]

)

Any ideas how to get the correct totals?

[whiteline · Accepted solution]

Hi.

Most likely, it's correct.  I suggest you just have some values of [Accident Counter] field that are connected with more than one [Critical Crashes] value.

Depending on what is 'correct' you have either:

1) Sum(aggr(sum({$<[Claim Company] = {'XXXX'}>}[Accident Counter]), [Critical Crashes], Quarter)) - to get 964 and so on.

2) Correct your data to eliminate duplicates - to get 953 as column total sum.