cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors