Forum Discussion
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!
hi, Anonymous
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%
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
3 Replies
hi, Anonymous
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%
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
v-lili6-msft that worked perfectly, I'll have to read up on the summarize function, really appreciate it!
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
