Crosstab - Totaling Values for Distinct Acct #? Totals Far too High

[Eric.Seitz]

Howdy folks!

I'm plugging away at an issue and I was hoping there's an easy answer I just missed.

I'm making a cross-tab of accounts and need to total both the original bill and payments, however with a standard "total" the original bill is far too high since it's totaling it several times.
For example it looks a little something like this

Invoice            Original            Payment         Date
0001                $1,000             $40               Jan 1
0001                $1,000             $20               Feb 2
0001                $1,000             $10               Mar 6
0002                $5,000             $20               Jan 3
0002                $5,000             $10               Jan 1
0002                $5,000             $5                 Apr 3
-----------------------------------------------
Total (Jan)        $18,000           $105

The total for the payments are correct but the total for "Original" is way too high. It should be $6,000 (1000+5000) but it's totaling all rows.

I've tried using 'distinct' when totaling (total (distinct [Original])) but if invoices have the same bill it skips over them still giving the wrong answer.
I've also tried total([Original] for [Invoice]) but that also doesn't do it since the report is split up by month and it gives me ALL the months at once.

What I need is a way to total up the original column based off of the invoice #

[bus_pass_man]

The pattern of values being repeated in several rows in a report with incorrect aggregation results suggests double counting.  This means that the query is being projected below the fact grain.

If you are using FM as your modelling tool then you need to ensure that proper determinants have been defined in your model.  If you are using a data module, you need to do the same thing for column dependency.