Using SumX like sumifs and getting totals sums

[dpibarker]

I have a data table with locations, area # and other measured data. 

Example:

Location	Area	Measure
Location A1	BK35	3079.809
Location A1	BK35	2814.287
Location A1	GI40	3743.701
Location A1	GI40	3544.88
Location A1	NL50	3449.787
Location A1	PL50	3445.053
Location A1	PL50	3435.71
Location A1	PL50	3160.638
Location A1	SW40	3153.577
Location B2	BC71	3127.085
Location B2	BH40	3063.513
Location B2	BH40	3061.719
Location B2	BH40	3043.09
Location B2	BL40	2997.417
Location B2	HG60	2936.687
Location B2	HG60	2898.549
Location C3	BC40	2868.828
Location C3	BH50	2820.418
Location C3	BH50	2651.894
Location C3	BH50	2415.948
Location C3	MU45	3537.022

I need to get the

1. Total Sum by Location

2. Total Sum by Location and Area

3. Percent: SumbyLocationArea / SumbyLocation

Summarized in Excel, this would be:

Total by Location				Total by Location & Area	Calculation
29827.44		Location A1	BK35	5894.096	19.76%
		Location A1	GI40	7288.58	24.44%
		Location A1	NL50	3449.787	11.57%
		Location A1	PL50	10041.4	33.66%
		Location A1	SW40	3153.577	10.57%
21128.06		Location B2	BC71	3127.085	10.48%
		Location B2	BH40	9168.322	30.74%
		Location B2	BL40	2997.417	10.05%
		Location B2	HG60	5835.235	19.56%
14294.11		Location C3	BC40	2868.828	9.62%
		Location C3	BH50	7888.26	26.45%
		Location C3	MU45	3537.022	11.86%

[sevenhills · Accepted solution]

Your output seems to be wrong in the percentage column ...

Sum per Location = CALCULATE( sum(Table1[Measure]) , ALLEXCEPT(Table1, Table1[Location]))

% within Location = sum(Table1[Measure])
/ CALCULATE( sum(Table1[Measure]) , ALLEXCEPT(Table1, Table1[Location]))