MicrosoftHas anyone figured out a way to do what effectively a GROUPBY() function would do?
For example, in this data:
Item Sales
| A | 1 |
| B | 1 |
| C | 1 |
| A | 2 |
| B | 3 |
| C | 4 |
It is easy to get a unique list of items: =UNIQUE(SampleData[Item])
But I cannot figure out how to dynamically get a subtotal of the items to return a table that would look like this:
| Item | Sales |
| A | 3 |
| B | 4 |
| C | 5 |
I know I can use the SUMIFS() formula in the Sales column easily, but that will not dynamically expand. If I add an item "D" in the original table, the UNIQUE() function will grab the info for the Item column, but the Sales column will be missing the total until I copy the SUMIFS() down.
I know the Sales column would need to be its own dynamic array function, but not sure how to get the first row to correspond to the first row of the Item column, the second row to the second, etc., or if it is even possible.
As a general rule, I prefer to do this in Power Query, which is easy, but that requires end users to do a refresh step. If they don't their reports are wrong. Dynamic arrays seems to be an ideal solution as it is automatically calculated.
I'd love to see a GROUPBY() function that was something like:
GROUPBY(DataRange,GroupBy1Column,GroupBy2Column,GroupBy3Column,....,Type1,Column1,Type2,Column2,Type3,Column3,....)
Where Type1 would be something along the lines of the SUBTOTAL() arguments, SUM, AVERAGE, MIN, MAX, etc. and once you started one of those, the function knew you were switching from the grouping columns to the summarization columns.
