I am trying to group a datasheet and return the sum of column a based on values in column b but I still need to return the sum of the complete column c so I can't filter the table prior to grouping. I just tried this:
Table.Group (
MyTable
, {
"ColumnD"
}
, {
{
"Sum Criteria"
, each List.Sum (
Table.Column (
Table.SelectRows (
_
, each [ColumnB] = "Foo"
)
, "ColumnA"
)
)
, Int64.Type
}
, {
"Sum Complete"
, each List.Sum ( [ColumnC] )
, type number
}
}
)
But the numbers aren't right, in my example the result of the criteria based column should be 15 but it's returning 35.
Can anyone please help me figure out where I am erring?
Thanks
I am really sorry for asking this question, the solution I used initially was right. my mistake was that I previewed the results by adding another column which contained all rows, and while I was previewing it, I counted them out and found that the numbers didn't match. When I expanded the table many more rows were revealed showing that the formula was accurate.
I wonder what the bug is with the query designer that it didn't show all the rows while previewing the table.
I am leaving the question so other people can see my formula and enjoy it.