cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cpe11 Frequent Visitor
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%

Capture.PNG

Any help on this would be greatly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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:

13.JPG

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%

 

If not your case, Please share sample pbix file or some data sample and expected output for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading

 

 

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
Super User

Re: Summing Sub Groups for Measure

Can you post sample data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
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:

13.JPG

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%

 

If not your case, Please share sample pbix file or some data sample and expected output for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading

 

 

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.
cpe11 Frequent Visitor
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!