3

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

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

My calculation is a 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 a field and then applies the max b multiplication after.

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

SonalPM
  • 1,317
  • 8
  • 17
Phil5p
  • 33
  • 1
  • 2
  • 5

2 Answers2

2

Had the exactly same issue few days ago. The actual behavior is correct, because it depends on the partitions (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 occurs 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. Not very hard to do on SQL, I guess. There is no way to get what you want (only in the lowest level aggregation) through data blending only

Inox
  • 2,255
  • 3
  • 13
  • 26
1

if you want to replicate the logic SUM(SUM(primary.a) × MAX(secondary.b)), I think you'll need an LOD expression that assigns the maximum of secondary.b to each row in primary.a.

I think an expression like:

{EXCLUDE[id]:MAX[secondary.b]}

… should do the trick.

Obsidian
  • 3,719
  • 8
  • 17
  • 30
Adrienne Tiley-Lerner
  • 11
  • 4