Expression Help

[rittermd]

I have invoice data.  Each invoice can have multiple lines each with a charge amount.  

My problem is that there is a field on each row that contains the total for the invoice.  I need to use this field in an expression but I can't sum it because it will return the total multiple for the number of lines present.

For example:

Line#    Amount  InvTotal

Line 1   10.00    30.00

Line 2    15.00    30.00

Line 3     5.00    30.00

I want to use InvTotal in an expression and should only be showing a value of 30 and not 90.  I am also going to sum the line amounts for another expression.  The sum of the lines could be different than the invoice total.

In a nutshell I only want to sum the total one time no matter how many lines that invoice has.

Thanks

[GaryGiles]

It kind of depends on where/how you want to use the calculation.

If you have Invoice_ID as a Dimension in your table, you could just use Avg(InvTotal), if it is the same value for an Invoice, it should work fine.

The following would work at an aggregate like to show the sum of all Invoice Amounts, without muliplying the values by the number of rows for each invoice.

sum(Aggr(Avg(InvTotal), Invoice_ID))