Price variance Actuals vs Budget in two separate tables

[pmxgs]

Hello, 

In a summarized way, my data is in single table, where I have the typical columns, like date, product, customer, sales, volume.

With this data setup, so far I've used the following approach to calculate variances between actuals and budget.

Volume Act= calculate (volume,phase="Actuals ")

Volume Bud= calculate (volume,phase="Bud ")

Avg Price Act = calculate (sales,phase="Actuals ")/Volume Act

Avg Price Bud = calculate (sales,phase="Budget ")/Volume Bud

Price Variance=sumx(values(product),(Avg Price Act-Avg Price Bud)*Volume_Act)

Now I will have two separate tables, one for actuals and another one for Budget (actuals has finer granularity for product codes, etc), but I want to keep calculating price variance at the same level (main product level).

I'm thinking of creating a single table using UNION (adjusting a bit the budget table so that I have the same structure as actuals), so that I keep calculating price variances the same way.

I would like to know if there is an alternative way of reaching the same goal, without having to join both tables using UNION.

thanks,

pm

[pmxgs]

Thanks for the solution provided.

I tested and it worked.

[FBergamaschi · Accepted solution]

I would keep the two tables separated

Connect the product table to both actuals and budget tables, create a volume measure and average measure on each (without calculate calls in them) and then the DAX code stays

sumx(values(product),(Avg Price Act-Avg Price Bud)*Volume_Act)

If this helped please give kudos and or marke it as a solution

Thanks

Best

FB

[pmxgs]

If I want to calculate price variance at customer and product level, would you recomend the same approach ?(both customer and product columns are present in actuals and budget tables) 

In this case I probably need to create an additional table with unique values of Product&Customer key?

thanks

[FBergamaschi]

Hello, yes that's correct

Please mark a solution if this is now complete

Thanks

Best regards

[FBergamaschi]

Sorry I just saw you already marked as solution

Thanks!

[pmxgs]

Yes, the solution for the first question is ok.

If I need to do rhe same calculation at product & customer level , do I need to have a related table with all the unique product&customer keys?

presently, sine I have everything in the same table, I have a calculated column  for the prod&cust key and use this in the values function. 
Since product and customer are different tables, what would be the best approach?

thanks