Active Member
April 17, 2024
I am new to using the new Pivotby and Groupby functions in excel, and every video out there is only using one value to either sum or average although they have multiple values in their data set that can be calculated. With Pivot tables you can drop in say price and sales, and get the average price and total sales. How can you do the same with pivotby or groupby?
July 16, 2010
Hi Ben,
In my Advanced Formulas course, I teach how you can use HSTACK or VSTACK to return multiple functions. e.g.:
=PIVOTBY(Table1[[#All],[Segment]], Table1[[#All],[Country]], Table1[[#All],[Sales]], HSTACK(SUM,PERCENTOF,AVERAGE), 3,,,,,, 0)
I hope that points you in the right direction.
Mynda
Active Member
April 17, 2024
Mynda
Thank you so much for your reply. I apologize for not artiuculating my question clearly. What I am hoping you can provide guidance on when you have price per unit and revenue in your data set, and you would like to use pivotby or groupby to determine the average price per unit and the sum of revenue. In short you would have two separate key values that you need to perform a calculation on.
I inserted a pivot table created using your dataset from your groupby tutorial. As you can see I have average unit sold and sum of sales. How can you do this in either groupby or pivotby?
[Image Can Not Be Found]
1 Guest(s)