6 Replies Latest reply on Feb 5, 2019 9:07 AM by Hari Ankem

# Total Values incorrect

Hi,

I know this is an age old question but I have not come across an answer that work for me. I have 2 data sources, a Tableau Data Source on our server and an Excel file. I am using a blended link between the 2. The Excel file has the codes that qualify for a discount. I multiply the Sales by the discount in the Excel file to get the Amount and then divide it again to get the Unit Value.

As we can see, not all codes qualify for a discount. The calculations work perfectly on a granular level, however when rolled up to the total, the Unit value is incorrect because Tableau is applying the same calculation at the Grand Total level.

This I understand. However, I need to know how to fix it rather urgently

If I do this calculation in Excel, the Amount comes to 84 020 which makes the weighted average Unit Value 0.3 not 570.

• ###### 1. Re: Total Values incorrect

Instead of blending, I would suggest that you join the 2 files as shown below:

You can then create calculated fields as shown below:

Amount:

SUM([Sales])*AVG(ZN([R/t]))

Unit Value:

IF COUNTD(STR([Code])+[Product])=1 THEN

AVG([R/t])

ELSE

[Amount]/SUM([Sales])

END

Here is the final output that you should now get:

Hope this helps. Updated workbook is attached.

• ###### 2. Re: Total Values incorrect

Hi,

Unfortunately, I can't join the 2 data sources because my primary data source is on the Tableau Server.  As far as I am aware, I have to use a blended join between a server data source and an Excel file.

I did however try your formulae and they still didn't produce the correct answer. What am I doing wrong?

• ###### 3. Re: Total Values incorrect

Make this your calculation for amount.

IF ISNULL(SUM([Sales])*AVG(ZN([Additional RpT (2) (File for Tableau)].[R/t]))) THEN 0 ELSE SUM([Sales])*AVG(ZN([Additional RpT (2) (File for Tableau)].[R/t])) END

• ###### 4. Re: Total Values incorrect

Sorry, but am not able to make it work with blending because the only way we can use the Rate from the blended data source is with aggregation. Whereas, what we need is to use it without aggregation and so am unable to proceed.

Why don't you download that published data source and use it directly in your workbook by joining it with the second source?

• ###### 5. Re: Total Values incorrect

Hi, I understand your logic and I share your frustration. However, will this not mean that I have to keep updating this download everyday?

• ###### 6. Re: Total Values incorrect

No, you can publish your workbook to the server and schedule a refresh the same way and frequency as the data source is getting refreshed currently.