cancel
Showing results for
Did you mean:
Frequent Visitor

## 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%

1 ACCEPTED SOLUTION
Super 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]))``````

Super 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]))``````

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors