Forum Discussion

Ben_jdc's avatar
Ben_jdc
New Member
1 year ago

Dynamic pivot table in Power BI

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's avatar
Prince0011
Solution Sage
1 month ago

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?