Tableau blended aggregation calculation issue

Question

I'm trying to blend 2 data sources in Tableau.

When a field is brought from the secondary data source into the calculated field in the primary data source it forces me to aggregate at that point.

My calculation is multiplication, and when Tableau subtotals, it's doing it in an interesting way:

Formula = SUM(primary.a) x MAX(secondary.b)

Expected subtotal method = SUM(SUM(primary.a) x MAX(secondary.b))

Actual behavior seen = SUM(SUM(primary.a)) x MAX(secondary.b)

It subtotals the field and then applies the max b multiplication after.

Any ideas about how to control the way this calculation is done? 

Answer 1

• The actual behavior of this problem is correct because it depends on the partitions. i.e dimensions in the worksheet you're using. Let me explain. Suppose you have table A and table B:

A:

Id  MeasureA

1   10

2   20

3   15

4   25

5   10

6   5

B:

Id  MeasureB

1   5

2   10

3   20

4   5

5   15

6   25

 

• Now if you drag Id to Rows, and your formula to Columns (in a bar chart), you should have something like:

Id  MeasureB

1   50

2   200

3   300

4   125

5   150

6   125

• For the first line, you'll have SUM(A.MeasureA) = 10, and MAX(B.MeasureB) = 5. And the multiplication is 50

• Now if you remove the Id from the rows, you'll have SUM(A.MeasureA) = 85, and MAX(B.MeasureB) = 25. And the multiplication is going to be 2,125, and not the 950 you are expecting (the sum of the values in my third table)

• Remember, the aggregations occur in the level of the dimensions that are explicitly on the worksheet.

• To get your results tight you're going to need to join the tables before connecting to Tableau. It is not very hard to do on SQL. It is not possible do you want (only in the lowest level aggregation) through data blending only.