Instead of blending, I would suggest that you join the 2 files as shown below:
You can then create calculated fields as shown below:
IF COUNTD(STR([Code])+[Product])=1 THEN
Here is the final output that you should now get:
Hope this helps. Updated workbook is attached.
Workbook for Tableau_v10.5.twbx 183.5 KB
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?
Workbook for Tableau.twbx 46.7 KB
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
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?
Hi, I understand your logic and I share your frustration. However, will this not mean that I have to keep updating this download everyday?
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.