2 Different Functions in GROUPBY & PIVOTBY Functions. Excel Magic Trick 1844 Part 2
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1844-...
Learn about how to use the GROUPBY and PIVOTBY functions to make single cell formula reports that have 2 functions in a single report.
Topics:
1. (00:00) Introduction to GROUPBY, PIVOTBY and the new Lambda Replacement Functions.
2. (00:15) GROUPBY Function.
3. (01:08) PIVOTBY Function.
4. (01:36) Confusing Labels in Report.
5. (01:49) Two Fields in the Row Area.
6. (02:01) Two Different Functions with Two Fields in Row Area: Labels are correct!
7. (02:32) Summary.
8. (02:55) Closing, Video Links.
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #groupby #excelformula #excelfunctions #excelfunctions #excelformulasandfunctions #lambda #pivotby
Excellent Mike, excellent team
Go Team!!!!!!
That HSTACK inclusion just came in handy. Thanks to you and @Thebhaskarjoshi.
Go Team!!!!!!
I am overwhelmed and amazed in the same time, can't wait to apply these mindblowing functions
Mind Blowing for sure : ) : )
AMAZING. Thank you EXCELISFUN Team!!! 👍👍👍
Go Team!!!!!!
Thanks amazing Mike for this EXCELlent video.
You are welcome, Syed Muzammil Mahasan Shahi!!!!!!
We appreciate to your quick update.
Boss is back
I am still in CA taking care of Mom for a few more months. I just was at home for a few days.
That is very close to what we want on the naming of columns in the report!
Can’t wait till my 50% get these functions to play with them.
I can't wait for you to get them as soon as possible!!!!!
Brilliant trick 👏 👌
Lucky we have a great Team!!!!!
More awesomeness, thanks Mike!!
Well said: awesomeness!!!! You are welcome, Chris M!!!!
Impressive, and we can build a matrix table report with only a few clicks using PIVOTBY (instead of building it with a complex LET solution), so impressive! Thanks a lot for this video! It's always a pleasure and an honor to learn from Mike!
Yes, single cell reports are sooooooo much easier now : ) : ) : ) You are welcome a lot for all the videos and related materials. It is an honor for me to make video stories for the Team!!!!
Awesome as usual Mike! 👏👏
I can't wait for these functions to be available to everyone! Thanks for sharing 😊😊
I can't wait for you to get them either!!!!
@@excelisfun 😊🤗
@excelisfun Thanks Mike! I tried these two variations also =GROUPBY(Region,HSTACK(F2:F31,F2:F31),HSTACK(SUM,AVERAGE),3)
=GROUPBY(Region,LET(r,Sales,r),HSTACK(SUM,AVERAGE,PERCENTOF,STDEV.P,MIN,MAX))
Amazing ... before that i was dealing with each calculation separately
No more : ) : ) : ) : ) : )
That solves THAT problem!! LOL. I assume you can rinse and repeat to add colums for a vast number of calculations, up to HSTACK maximum.
Rinse and repeat lol
Once you add HSTACK, it's time to drop the mic (Mike 😉). 🎤 Thanks Mike!
Another great video.
A couple of oddities I've noted
An "old school" (lol) LAMBDA like below provides one column of aggregation
=GROUPBY(A2:A102,C2:C102,LAMBDA(r, SUM(r )),3)
As shown in your video, below provides two columns of aggregation
=GROUPBY(A2:A102,C2:C102,HSTACK(SUM,COUNT),3)
But combining these approaches returns one column of #VALUE! errors
=GROUPBY(A2:A102,C2:C102,LAMBDA(r, HSTACK(SUM(r),COUNT(r))),3)
If you have 2 value columns in PIVOTBY, then total columns appear on the right, but both are blank
=PIVOTBY(A2:A102,B2:B102,C2:D102,SUM)
I am not sure if the HSTACK trick works in LAMBDA...
I love all these tricks and always a pleasure to listen to Mike’s enthusiasm. However, I am a little at a loss why Microsoft Excel developers are deploying all these fancy formulas when we have good old traditional pivot tables available. Who are they catering to? In my view, 95% of the people use less than 5% of Excel 2016 (not even 2019/365) functionality. Will these end up being simply curiosities?
That is so cool trick. Thanks Mike and Bhaskar! However, when we select two fields (say sales and CoGS), one field is summed and another field is averaged. We have to see, whether there is a way to find both sum and average for both the columns.
the two columns in row are not separate. They work together in an AND Logical Test. So I don't think it is possible with these functions. Of course, we can build any single cell report with the many other wonderful functions : )
However, if you put one column in row and the other in column, you can sorta get what you want.
@@excelisfun @user-ju1it8wf2j
Try this =GROUPBY(Region,HSTACK(F2:F31,F2:F31),HSTACK(SUM,AVERAGE),3)
=GROUPBY(Region,LET(r,Sales,r),HSTACK(SUM,AVERAGE,PERCENTOF,STDEV.P,MIN,MAX))
Mike, Since 3 months ago I couldn't resolve this problem which is:
I have 4 columns in power Bi table 😮and I want to get average,median and stdev of each row of those 4 columns using DAX, not power query,
finally,I have got that's needs some magic.
So,I am writing for my #1 P4P magician.
I can't help right now becasue I am down in CA and taking care of my Mom. Try this amazing site for questions: mrexcel.com/board
@excelisfun God willing, your mother will be in better and stronger.
I will pray and ask God to make your mother better.
Gave her Salam from someone his life has been changed by you Mike
Hey Amigo thanks for this amazing video but i see that this fuction is not available for excel 365 desktop.. is it just for excel 365 enterprise available?
Just wanted to comment that GroupBy crashes Excel when slicers are attached to results. Others are having this problem as well. I thought it was just me! MS has been informed by some heavy-hitters as to the bug. I'm going to try it with your XLOOKUP idea now. I guess that's why they call it "beta".
Hello :)
So here is a question:
1:58
2 criteria and 2 sum ranges selected, 2 formulas chosen, so to me this should spit out 4 result columns and 2 criteria columns:
Criteria columns: as they are region and sales rep
now for the 2 formulas chosen the result currently is sum and average
but what if based on given criteria we need:
total sum (provided)
total avg sales (not provided)
total sum COGS (not provided)
total avg COGS (provided)
any ideas how to achive this without LET / LAMBDA?
I see what you are saying: like we can drag a sales field twice into pivot and have different calculations on each. I am not sure how to drag sales field twice into function and I am away from the computer that has these two functions to try it. Maybe you can play around and post back. Otherwise it may be a day or two till I can try.
@@excelisfun i also would need time, ill try at work tomorrow.
Basically we have 2 values columns and 2 functions - that should result in 4 calculations,
each calculation for each value column
so if we would put Sum, average and min, then we should get 6 results.
so on and so forth
where i found this function in excel? i could not get in office 365
Mr. mike, why i can't download this video... all video are ok .thanks
Hold on… you have summarized 2 columns (sales & cogs), and you have 2 functions (sum &average), but only 2 columns of values, shouldn’t you have 4 columns? It looks like you have the sum of sales and average of cogs.
Nope. Conditions in the row area of report are always run in an AND Logical Test. For example, in row 1 the logical test for each calculation is:
Region = "East" AND SalesRep = "Ahmed". 01:51 in the video makes refers to the AND Logical Test.
I understand that, I’m referring to the values. It looks like the report is showing the sum of the Sales, and the average of COGS. Don’t you want to show the sum of Sales & COGS and the average of Sales & COGS?
@@RonDavidowicz I see what you are saying: like we can drag a sales field twice into pivot and have different calculations on each. I am not sure how to drag sales field twice into function and I am away from the computer that has these two functions to try it. Maybe you can play around and post back. Otherwise it may be a day or two till I can try.