Use slicers with PIVOTBY, GROUPBY & FILTER in Excel

Use slicers with PIVOTBY, GROUPBY & FILTER in Excel | Excel Off The Grid

Рет қаралды 16,688

Күн бұрын

Пікірлер

@stevereed5776 10 ай бұрын

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!!

@ExcelOffTheGrid 10 ай бұрын

The good news is that you can use this technique with FILTER right now. 👍

@SimplyDigitalLK 2 ай бұрын

Yes, for sure the combination of Pivotby and Groupby with slicer - is absolutely great!!

@ivanbork4175 10 ай бұрын

Very interesting, and very well introduced. I´m looking forward to the update that include this function.

@ExcelOffTheGrid 10 ай бұрын

Hopefully it comes soon, but might be a little while yet.

@IvanCortinas_ES 10 ай бұрын

Spectacular BYROW-LAMBDA-SUBTOTAL combination. Thanks for sharing Mark. Excellent tool research.

@ExcelOffTheGrid 10 ай бұрын

Glad you enjoyed it. I've got more on that function combination coming next week too. 👍

@DataVisualisation 10 ай бұрын

Very elegant and fast solution 👏

@ExcelOffTheGrid 10 ай бұрын

Thank you! 😊

@Excelvenky 10 ай бұрын

This is amazing and totally speechless 🤩.. gratitude and thanks

@ExcelOffTheGrid 10 ай бұрын

Thank you. I’m glad you liked it 😁

@AdolfoLeonSepulveda 3 ай бұрын

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.

@whowhatwhenwhyhow9265 10 ай бұрын

Thank you. Great video, great teacher.

@ExcelOffTheGrid 10 ай бұрын

Thank you. I’m glad you liked it.

@collisionn 2 ай бұрын

Thank you Mark, this is one of the few videos that are not click-bait for basic Slicer integrations :) Food for thought! I'm still wrapping my haead around the PIVOTBY function, will rewatch the video another hundred times. I was hoping for the Slicer to be affected by the FILTER function as well. =FILTER(Table_AgentData,(Table_AgentData[Agent Region]="FRANCE")*(BYROW(Table_AgentData[Eligibility], LAMBDA(r,SUBTOTAL(3,r)))),"Nope, feel the void") Filtering for France in the FILTER funcion, I am looking this to pre-filter the Slicer for that region. Any tips? Hope this makes sense. Cheers!

@ennykraft 5 ай бұрын

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?

@VenemAcademy 2 ай бұрын

Pivotby and Groupby with slicers are great, but is it possible to sort (asc or dec) on the values? This is more informative and suport conclusions better

@prathamsuthar4977 18 күн бұрын

great video!

@valeriastrafe5104 9 ай бұрын

Cant wait for these formulas to be available in my Excel version

@UtuDudas Күн бұрын

Fantastic

@MichaelBrown-lw9kz 10 ай бұрын

Wow, this is impressive.

@ExcelOffTheGrid 10 ай бұрын

Thanks - I really like this slicer technique, there is a lot of potential with it.

@jerrydellasala7643 10 ай бұрын

Very nice!

@ExcelOffTheGrid 10 ай бұрын

Thank you! Cheers!

@VenemAcademy 10 ай бұрын

Thnx Mark. Is this new function the traditional pivot table killer? Does this also work with datamodels through Power Pivot?

@ExcelOffTheGrid 10 ай бұрын

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.