Dear BI Users,
In the table below, I have previous period and current period data that I am trying to use to calculate a Price Variance Measure.
| July 2019 Retail (Problem set) | | | | | | | | | |
| | | Previous Period | | Current Period | Price Variance |
| | | Cost | Materials | Cost per Material Previous | | Cost | Materials | Cost per Material Current | # of Sales Current | Price Variance |
| Designer 1 | | | | | | | | | | |
| Store 1 | | | | | | | | | | |
| | Lamb Leather | $ 5,000.00 | 2 | $ 2,500.00 | | $ 5,500.00 | 2 | $ 2,750.00 | 250 | $ (62,500.00) |
| | Cow Leather | $ 1,500.00 | 1 | $ 1,500.00 | | $ 5,250.00 | 3 | $ 1,750.00 | 300 | $ (75,000.00) |
| | Canvas | $ 2,000.00 | 2 | $ 1,000.00 | | $ 1,250.00 | 1 | $ 1,250.00 | 500 | $ (125,000.00) |
| Subtotal | | $ 8,500.00 | 5 | $ 1,700.00 | | $ 12,000.00 | 6 | $ 2,000.00 | 1050 | $ (315,000.00) |
| Store 2 | | | | | | | | | | |
| | Lamb Leather | $ 2,200.00 | 1 | $ 2,200.00 | | $ 2,450.00 | 1 | $ 2,450.00 | 300 | $ (75,000.00) |
| | Cow Leather | $ 5,100.00 | 3 | $ 1,700.00 | | $ 3,900.00 | 2 | $ 1,950.00 | 350 | $ (87,500.00) |
| | Canvas | $ 2,400.00 | 2 | $ 1,200.00 | | $ 2,900.00 | 2 | $ 1,450.00 | 550 | $ (137,500.00) |
| Total | | $ 9,700.00 | 6 | $ 1,616.67 | | $ 9,250.00 | 5 | $ 1,850.00 | 1200 | $ (280,000.00) |
| Grand Totals | | $ 18,200.00 | 11 | $ 1,654.55 | | $ 21,250.00 | 11 | $ 1,931.82 | 2250 | $ (623,863.64) |
The following three fields are measures while the rest are normal columns.
Cost per Materials Previous = [cost Current]*[Materials Current]
Cost per Materials Current = [cost]*[Materials]
Price Variance = ([Cost per Materials Previous] – [Cost per Materials Previous]) * [# of Sale]
The issue I am having can be seen in the table above, in which the subtotals are not providing the sum of the values in each store, but rather calculating the measure using the subtotals of the columns within the measure. So, the subtotal of “Cost Per Material” is actually dividing the subtotal of the Cost column by the subtotal of the Materials column. The same is the case for the Price Variance function as well. This is throwing off the value of the grand total for all stores. I've tried numerous functions including various iterations of SUMX and have found no solution to getting an accurate sum of these measures.
My desired solution is seen in the table below, in which the Subtotal row equals the sum of the above values for each store, and all subtotals combine to a sum grand total.
| July 2019 Retail (Desired Solution) | | | | | | | | |
| | | Previous Period | | Current Period | Price Variance |
| | | Cost | Materials | Cost per Material Previous | | Cost | Materials | Cost per Material Current | # of Sales Current | Price Variance |
| Designer 2 | | | | | | | | | | |
| Store1 | | | | | | | | | | |
| | Lamb Leather | $ 5,000.00 | 2 | $ 2,500.00 | | $ 5,500.00 | 2 | $ 2,750.00 | 250 | $ (62,500.00) |
| | Cow Leather | $ 1,500.00 | 1 | $ 1,500.00 | | $ 5,250.00 | 3 | $ 1,750.00 | 300 | $ (75,000.00) |
| | Canvas | $ 2,000.00 | 2 | $ 1,000.00 | | $ 1,250.00 | 1 | $ 1,250.00 | 500 | $ (125,000.00) |
| Subtotal | | $ 8,500.00 | 5 | $ 5,000.00 | | $ 12,000.00 | 6 | $ 5,750.00 | 1050 | $ (262,500.00) |
| Store 2 | | | | | | | | | | |
| | Lamb Leather | $ 2,200.00 | 1 | $ 2,200.00 | | $ 2,450.00 | 1 | $ 2,450.00 | 300 | $ (75,000.00) |
| | Cow Leather | $ 5,100.00 | 3 | $ 1,700.00 | | $ 3,900.00 | 2 | $ 1,950.00 | 350 | $ (87,500.00) |
| | Canvas | $ 2,400.00 | 2 | $ 1,200.00 | | $ 2,900.00 | 2 | $ 1,450.00 | 550 | $ (137,500.00) |
| Subtotal | | $ 9,700.00 | 6 | $ 5,100.00 | | $ 9,250.00 | 5 | $ 5,850.00 | 1200 | $ (300,000.00) |
| Grand Totals | | $ 18,200.00 | 11 | $ 10,100.00 | | $ 21,250.00 | 11 | $ 11,600.00 | 2250 | $ (562,500.00) |
Any help or guidance you can provide would be greatly appreciated!
