Forum Discussion
Table.Group by aggregate from a RELATED column
I have a MSSDates table that has a relationship with my calendar table. The MSSDates table has a RELATED column in it to identify the day as a working day or not, I want to create a summary table that groups by opportunity and shows the total number of working days for each opportunity. Because the working days column is a related column in the table, I can't do it in "Transform Data" becasue the column does not show as an option to aggregrate. So I need to write the syntax for this in create table. I just can't get the syntax right. All I need is
Table.Group(Select.Columns(Project,Opportunity, WorkignDays)
group by Project, Opportunity, Sum(WorkingDays)
GOT IT! take out the second SUM
Table = GROUPBY(MSSFlatFeeDates,MSSFlatFeeDates[Opportunity],"TotalDays",SUMX(CURRENTGROUP(),MSSFlatFeeDates[WorkingDay]))
2 Replies
So - I got this far- but I'm not getting the right number - there should be 252 - 254 value (working days in a year) for each opportunity. Any pointers? thx in advance
Table = GROUPBY(MSSFlatFeeDates,MSSFlatFeeDates[Opportunity],"TotalDays",SUMX(CURRENTGROUP(),SUM(MSSFlatFeeDates[WorkingDay])))GOT IT! take out the second SUM
Table = GROUPBY(MSSFlatFeeDates,MSSFlatFeeDates[Opportunity],"TotalDays",SUMX(CURRENTGROUP(),MSSFlatFeeDates[WorkingDay]))