Multiply a column by a constant value per year in Tableau

Question

I have an Excel file with normalized structure:

[table1]
id    percentage    region    year
1     0.10            A         01-01-1995
2     0.61            A         01-02-1995
3     0.97            A         01-03-1995
4     0.11            B         01-01-1995
5     0.21            B         01-02-1995
6     0.99            B         01-03-1995
7     0.02            A         01-01-1996
8     0.61            A         01-02-1996
9     0.96            A         01-03-1996
10    0.05            B         01-01-1996
11    0.55            B         01-02-1996
12    0.99            B         01-03-1996

and a second table, with

[table2]
other_id     region    value         year
 1           A          99          1995
 2           A          76          1996
 3           B          102         1995
 4           B          50          1996

and now I need to multiply the values from the first table to the second table, to get the total by month by year evolution. I tried to do a calculated field like

[table1] * [table2]

but I get the not aggregated error. The calc

avg[table1] * avg[table2]

or

attr[table1] * [table2]

is valid in Tableau, but the result is not correct. How can I accomplish this directly on Tableau?

EDIT: The expected result would be

    [table1]
id    percentage    region    year
1     0.10 * 99       A         01-01-1995
2     0.61 * 99       A         01-02-1995
3     0.97 * 99       A         01-03-1995
4     0.11 * 102      B         01-01-1995
5     0.21 * 102      B         01-02-1995
6     0.99 * 102      B         01-03-1995
7     0.02 * 76       A         01-01-1996
8     0.61 * 76       A         01-02-1996
9     0.96 * 76       A         01-03-1996
10    0.05 * 50       B         01-01-1996
11    0.55 * 50       B         01-02-1996

Answer 1

You can try creating a formula. It can go something like this-

For each row in table1,
  Find a corresponding row in table2 (with same year and region).
  Multiply the percentage in table1 with value in table2