Thank you Mark, great to see a different use of the PIVOTBY function (rather than the usual videos) and with slicers, this makes it very powerful. Can't wait to get hold of it now!!
You're the GOAT! Thank you! Could you maybe do another video about all the different calcations in GROUPBY and PIVOTBY? I love what ARRAYTOTEXT in combination with SUBSTITUTE does. Maybe examples for LAMDA? Another question: I've noticed that in my version there's an extra option in GROUPBY called field_relationship. Do you know what that one does?
Many thanks Curious note: If the col_total_depth argument in PIVOTBY is set to zero to hide row totals and the BYROW(Data[Value];LAMBDA(r;SUBTOTAL(3;r))) argument is removed from filter_array, the slicers stop working. Therefore, whether row totals are shown or not, it is necessary to include BYROW in filter_array for the slicers to function properly.
Yes, this is the new function. Personally, I don’t think it will be a PivotTable killer. It doesn’t work directly with Power Pivot. You would need to spill some CUBE functions, then work with that spill range. So, I’m sure there could be ways, but probably quite flakey & difficult.
Thank you Mark, great to see a different use of the PIVOTBY function (rather than the usual videos) and with slicers, this makes it very powerful. Can't wait to get hold of it now!!
The good news is that you can use this technique with FILTER right now. 👍
Yes, for sure the combination of Pivotby and Groupby with slicer - is absolutely great!!
Very interesting, and very well introduced.
I´m looking forward to the update that include this function.
Hopefully it comes soon, but might be a little while yet.
Thank you. Great video, great teacher.
Thank you. I’m glad you liked it.
You're the GOAT! Thank you!
Could you maybe do another video about all the different calcations in GROUPBY and PIVOTBY? I love what ARRAYTOTEXT in combination with SUBSTITUTE does. Maybe examples for LAMDA?
Another question: I've noticed that in my version there's an extra option in GROUPBY called field_relationship. Do you know what that one does?
This is amazing and totally speechless 🤩.. gratitude and thanks
Thank you. I’m glad you liked it 😁
Very elegant and fast solution 👏
Thank you! 😊
Spectacular BYROW-LAMBDA-SUBTOTAL combination. Thanks for sharing Mark. Excellent tool research.
Glad you enjoyed it. I've got more on that function combination coming next week too. 👍
Many thanks
Curious note: If the col_total_depth argument in PIVOTBY is set to zero to hide row totals and the BYROW(Data[Value];LAMBDA(r;SUBTOTAL(3;r))) argument is removed from filter_array, the slicers stop working. Therefore, whether row totals are shown or not, it is necessary to include BYROW in filter_array for the slicers to function properly.
Cant wait for these formulas to be available in my Excel version
Wow, this is impressive.
Thanks - I really like this slicer technique, there is a lot of potential with it.
Very nice!
Thank you! Cheers!
Thnx Mark. Is this new function the traditional pivot table killer? Does this also work with datamodels through Power Pivot?
Yes, this is the new function. Personally, I don’t think it will be a PivotTable killer.
It doesn’t work directly with Power Pivot. You would need to spill some CUBE functions, then work with that spill range. So, I’m sure there could be ways, but probably quite flakey & difficult.
do you have a complete course for VBA? i'd love to attend and even pay for that
Sorry, we don't have a VBA course. It's on the roadmap, but might be a way off yet.
Mark, How to use coupon code in your academy?
If you reach out to customer support.we can help you with any questions.
exceloffthegrid.com/customer-support
🌹🌹
Thanks Kebin 😁