Price Variance Actuals vs Budget in separate tables (calculated at Product & Customer Level)

[pmxgs]

Hello, 

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

Scenario	Customer	Product	Volume	Sales	prod-cust_key
Actual	A	p1	5	10	Ap1
Actual	A	p2	7	15	Ap2
Actual	B	p1	9	23	Bp1
Actual	B	p1	7	16	Bp1
Budget	A	p1	5	10	Ap1
Budget	A	p2	7	15	Ap2
Budget	B	p1	9	23	Bp1
Budget	B	p1	7	16	Bp1

With this data setup, so far I've used the following approach to calculate variances between actuals and budget (at customer & product level).

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(prod-cust_key),(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 & customer level).

I'm thinking of creating a single table using UNION (adjusting the budget table to 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

[MasonMA · Accepted solution]

@pmxgs 

There are, but I would stick with the UNION approach since you’re calculating at a higher level (e.g., product & customer) and already using prod-cust_key, unless you have a business constraint that prevents merging the tables...

[bhanu_gautam]

@pmxgs 

Create relationships between Actuals and ProductCustomer, and Budget and ProductCustomer on Customer and Product.

Define measures to calculate the actual and budget volumes and sales.

Volume_Act = CALCULATE(SUM(Actuals[Volume]))
Sales_Act = CALCULATE(SUM(Actuals[Sales]))
Volume_Bud = CALCULATE(SUM(Budget[Volume]))
Sales_Bud = CALCULATE(SUM(Budget[Sales]))

Define measures to calculate the average prices for actuals and budget.

Avg_Price_Act = DIVIDE([Sales_Act], [Volume_Act])
Avg_Price_Bud = DIVIDE([Sales_Bud], [Volume_Bud])

Finally, define a measure to calculate the price variance.

Price_Variance = SUMX(
VALUES(ProductCustomer[prod-cust_key]),
([Avg_Price_Act] - [Avg_Price_Bud]) * [Volume_Act]
)