Calculate Variance between two categories

[domtrump]

So I have a power pivot table set up where I Two categories "1-12 Months" and "13-24 Months" Then I have a bunch of measures. I need to create a variance that subtracts all of the 13-24 Month measures from all of the 1-12 Month measures. What is the best way to accomplish this?

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	*	*	Period	Data	*	*	*	*	*	*	*	*	*	*	*	*	*	*
2	*	*	1-12 Months	*	*	*	*	*	*	*	13-24 Months	*	*	*	*	*	*	*
3	State	County	Patrons	Days	Play Days	Theo	Actual	Comps	Bus E-Promo	E-Promo	Patrons	Days	Play Days	Theo	Actual	Comps	Bus E-Promo	E-Promo
4	NJ	Atlantic County	8,336	135,538	127,304	9,208,349	9,385,971	1,776,926	84,170	1,326,739	9,433	120,766	113,678	8,005,025	7,390,065	1,329,202	129,005	1,313,441
5	*	Bergen County	8,087	29,579	28,214	4,248,509	5,243,032	1,247,943	211,605	900,947	8,104	26,124	25,090	3,759,987	3,982,836	1,036,270	171,835	596,650
6	*	Burlington Coun	5,197	23,382	22,628	2,459,834	2,394,601	518,058	91,205	443,414	5,689	21,479	20,736	1,776,701	1,841,167	350,652	91,196	326,007
7	*	Camden County	7,183	41,899	40,273	4,095,485	4,173,976	865,510	174,700	797,460	7,808	35,441	34,167	3,097,980	3,191,898	599,426	163,774	575,353
8	*	Cape May County	1,770	13,858	13,136	1,549,104	1,537,671	325,455	50,785	294,887	1,851	12,782	12,208	1,578,892	1,575,169	330,254	56,795	271,088
9	*	Cumberland Coun	2,079	16,324	15,766	1,381,330	1,408,105	217,719	60,945	262,207	2,212	13,210	12,774	1,013,276	996,078	139,409	65,290	238,378
10	*	Essex County	8,401	24,430	23,639	2,295,582	2,213,431	443,330	209,505	487,785	7,187	20,580	19,850	1,888,583	1,753,393	415,847	131,965	309,780
Sheet1

 

[Gordonik]

Is this necessary to do a separate measures for each country?
Maybe one measure per "patrons","days" and so on be enough. You could be able to filter it by countries in a pivot.
Then you can easly do 8 measures:
Variance (Petrons):=[1-12 Months & Petrons] - [13-24 Months & Petrons]
Variance (Days):=[1-12 Months & Days] - [13-24 Months & Days]
...

 

[domtrump]

Is this necessary to do a separate measures for each country?
Maybe one measure per "patrons","days" and so on be enough. You could be able to filter it by countries in a pivot.
Then you can easly do 8 measures:
Variance (Petrons):=[1-12 Months & Petrons] - [13-24 Months & Petrons]
Variance (Days):=[1-12 Months & Days] - [13-24 Months & Days]
...

What would be the syntax for such a measure? "1-12 Months" and "13-24 Months" are two values for my [timeframe] column.

 

[Gordonik]

Just to clarify. Do all cells with figures in your set are measures? So there are the data? Please attach some.
On the beginning You should reduce a number of measures. Why there are separate measures for each country? You can do one measure for let say "Petrol" and cross filter it later in a pivot by countries, and whatever you have got in your source data. Let's call this measure "1-12 Months & Petrons" - It means, it is dedicated to Petrol and 1-12 Months period. Then you can create a similar one for "13-24 Months & Petrons". At the end subtract one from another