cancel
Showing results for
Did you mean:
Frequent Visitor

## Summing Sub Groups for Measure

Hey All,

I have a report that contains groups, some of which have subgroups, and I need to calculate everything at the primary group level. The primary metric I'm having an issue with is the Exceeding Goal % which has two inputs, the number of groups exceeding the goal divided by the number of active months. Those measures are currently defined as such:

```Active Months =
var active = FILTER(VALUES(dimGroup[GroupName]),[Active Ind Sum]>0)
return COUNTROWS(active)

Exceeding Goal =
var Exceed = FILTER(values(dimGroup[Group Name]),[Goal Surplus]>0)
return COUNTROWS(Exceed)```

The use of countrows allows for my needed subgroup logic to take action - if one or more subgroups is active or exceeding goal then that counts as just 1 for both metrics.

The issue comes when I try to view the metric outside of a Group context - because it's based off of countrows the inputs do not sum correctly and therefore the metric outside of context (56% below) is off -- should be (7+1) / (5+9) = 64.3%

Any help on this would be greatly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Summing Sub Groups for Measure

hi, @cpe11

This looks like a measure totals problem.See the post about it here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

And I have used your formulas and my sample data do a test.

```Measure = var _table=SUMMARIZE(dimGroup,dimGroup[Month Name],"a",[Active Months]) return
CALCULATE(SUMX(_table,[a]))

Measure 2 = var _table=SUMMARIZE(dimGroup,dimGroup[Month Name],"b",[Exceeding Goal]) return
CALCULATE(SUMX(_table,[b]))

Measure 3 = [Measure 2]/[Measure]```

Result:

For measure total is 9+5=14

For measure 2 total is 7+2=9

For measure total is (7+2) / (9+5)=64% not 4 / 9=44%

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User

## Re: Summing Sub Groups for Measure

Proud to be a Datanaut!

Community Support Team

## Re: Summing Sub Groups for Measure

hi, @cpe11

This looks like a measure totals problem.See the post about it here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

And I have used your formulas and my sample data do a test.

```Measure = var _table=SUMMARIZE(dimGroup,dimGroup[Month Name],"a",[Active Months]) return
CALCULATE(SUMX(_table,[a]))

Measure 2 = var _table=SUMMARIZE(dimGroup,dimGroup[Month Name],"b",[Exceeding Goal]) return
CALCULATE(SUMX(_table,[b]))

Measure 3 = [Measure 2]/[Measure]```

Result:

For measure total is 9+5=14

For measure 2 total is 7+2=9

For measure total is (7+2) / (9+5)=64% not 4 / 9=44%

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Summing Sub Groups for Measure

@v-lili6-msft that worked perfectly, I'll have to read up on the summarize function, really appreciate it!