Forum Discussion
Using SumX like sumifs and getting totals sums
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% |
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]))
1 Reply
sevenhillsSuper User
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]))