How to apply % of total sales from one table to another table

[Anonymous]

Hi Everyone,
I have two separate tables, Table A (Budgeted sales by State & Employee) & Table B (Budget sales by Business Unit & Product Category). There is no common values between two tables except months and both totals same which is total budgeted sales for the year.
I want combine these two tables in one so that I can slice by State, Employee, Business Unit & Product Category.
I have tried calculating $ of total budgeted sales for each month for state & employee from Table A and applied that measure to Total Budgeted Sales in Table B.
It gives me correct value in total for the year but for each month, it gives % of that month’s Total Budgeted Sales. (Eg. For Jan-21 - Budgeted Sales was $1000, it gives me 5% of $1000, i.e. $50. Where as in total for the year, it gives me correct amount).

What would be the best solution in such scenario?

[lbendlin]

Best solution would be to have a transaction identifier in both tables.

Second best solution would be to combine these two tables at the data source by adding all granularities directly. Meaning each row of your data would identify state, employee, business unit and product category.

If you cannot do that then you have to live with the ambiguity of your current data model.