Dynamic pivot table in Power BI

[Ben_jdc]

Hello,

Is there a way to create a visual in power bi like a pivot table in Excel , where the fields are dynamic and the end - user can build a diffrent pivot table with diffrent fields every time. ?

[Prince0011]

Yes, but not exactly like Excel.

Power BI does not currently allow end users to drag and drop fields into a visual the way they can in an Excel PivotTable. However, there are several features that provide similar flexibility:

1. Field Parameters (Recommended)

Field Parameters let report consumers dynamically switch dimensions and measures using a slicer. This is the closest built-in feature for creating dynamic reports without editing the report.

Best for: Allowing users to choose which fields appear in rows, columns, or values.

2. Matrix Visual

The Matrix visual supports:

• Expand/Collapse hierarchies

• Drill Down and Drill Up

• Dynamic row and column groupings

• Subtotals and grand totals

Combined with Field Parameters, it provides a very flexible pivot-like experience.

3. Personalize Visuals

If the report designer enables Personalize visuals, users can:

• Replace fields in supported visuals

• Change aggregations

• Customize the visual for their own analysis without modifying the original report

This is the closest experience to building a custom PivotTable directly in the report.

4. Slicers and Bookmarks

Using slicers together with bookmarks or Field Parameters allows users to explore different perspectives of the data while keeping the report intuitive.

If You Need a True Excel-Style Pivot Experience

Power BI currently does not support a fully interactive drag-and-drop PivotTable interface where report consumers can freely move any field between Rows, Columns, Filters, and Values at runtime.

If that level of flexibility is required, consider:

• Analyze in Excel, which connects Excel PivotTables to the Power BI semantic model.

• Power BI Desktop for report authors who can modify visuals and fields.

Recommendation

For most scenarios, the best combination is:

• ✅ Matrix Visual

• ✅ Field Parameters

• ✅ Personalize Visuals (enabled)

Together, these provide a highly dynamic reporting experience while maintaining report performance and governance.

Has anyone in the community found other creative approaches or custom visuals that closely replicate Excel's drag-and-drop PivotTable experience?

[PBIdashboards]

Worth adding Flexa Tables as another option alongside FlexiPivot for dynamic pivot use cases in Power BI Service.

Key differences worth knowing when choosing between them: Flexa Tables also includes built-in variance columns (MoM, YoY, Budget vs Actuals) as one-click buttons  useful if the dynamic pivot need is tied to period comparison reporting. Both are Microsoft-certified on AppSource. Free trial: https://appsource.microsoft.com/en-us/product/power-bi-visuals/devspearllc1670524393721.flexa_tables

[pbisolution]

It's possible to create a dynamic pivot table with the FlexiPivot visual. Browse Apps | Microsoft Marketplace

End users can interact with the pivot table similar to how they interact with pivot tables in Excel. 

[qqqqqwwwweeerrr]

Hi @Ben_jdc 

This might bot be exactl solution this will guve you direction here is link for solution 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards

[DevSpear]

I think the illustrative example in the video is exactly what you're looking for. You can try the product here:
https://flexaintel.com/Power-BI-dashboard-examples-PBIX/bp-oil-pipeline-accidents-key-patterns-2010%...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[suparnababu8]

Hi @Ben_jdc  

While it is not currently possible to replicate the exact functionality of an Excel pivot table with user-selected fields in Power BI, you can create a dynamic pivot table-like visual using the Matrix visual and Field Parameters. 

I recommend using the Field Parameters concept. This will enable users to select the specific fields they wish to view by choosing them in a slicer. For more information on how to create Field Parameters, you can refer to the following URLs

https://learn.microsoft.com/en-us/power-bi/guidance/data-translation-implement-field

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

https://www.sqlbi.com/articles/fields-parameters-in-power-bi/

https://community.fabric.microsoft.com/t5/Desktop/Dynamic-Pivot-with-DAX/td-p/3442495

https://www.mssqltips.com/sqlservertip/7909/dynamically-create-tables-in-power-bi-with-dax-functions...

Thanks

[lbendlin]

Let users personalize visuals in a report - Power BI | Microsoft Learn