4 Replies Latest reply on Dec 14, 2011 6:39 AM by Joe Mako

# Problems with Totals

I am having problems with totals within my tableau report and was looking for help to get round about it. I have been asked to report on income on some coaching courses for the organisation I work for. However when using the tables from our bookings & pos database I noticed payments are doubled when the booking is spread over 2 weeks but with 1 payment. This is highlighted when you look at the booking ID. April 1st payment should only be £22. When you use the grossproductvalue for the sum it shows £44 because we have 2 booking ids for this one class. I need it to show £22. Therefore I created a calculation which you can see within my attached sample, however this calculation also applies when I add totals. I need th grand total to show the true total which I work out to be 199 and not the 211.4 it shows.

Any help would be greatly appreciated.

Many thanks

• ###### 1. Re: Problems with Totals

This seems to be a data quality issue.

The problem is that the calculation that you created is also applied at the Grand total level.  Thus the aggregate Product Gross Value (221) is divided by the aggregate count of Booking ID (12) and multiplied by the aggregate distinct count of Sales Invoice ID (22).

If is is a one-off anomaly, you can try this formula

case [SalesInvoiceID]

when 200204397 then 11

else [ProductGrossValue]

end

This will assign a value of 11 (instead of 22) to sales invoice 200204397.

• ###### 2. Re: Problems with Totals

1. place "SalesInvoiceID" on the Level of Detail shelf

2. create a calculated field with a formula of:

```IF FIRST()==0 THEN

WINDOW_SUM(AVG([ProductGrossValue]),0,IIF(FIRST()==0,LAST(),0))
END
```

3. place this calc field on your text shelf instead of your "Calculation1"

4. set this pill's compute using to "SalesInvoiceID"

Is that what you are looking for?

• ###### 3. Re: Problems with Totals

Hi Thanks to both for your help. Joe, your formula works perfect and gives me the result im looking for. Many thanks again. Can you explain to me how this formula works? Im trying to understand it but having difficulty.