So Your Detailed/Flat Pivot is Slow and Doesn’t Sort Properly? Try Text Measures!

Post by Rob Collie

Detailed Pivot Report Using Flattened Pivot

Does Your Pivot Look Like This?  Does its Slow make you Sad?  Time for a Fix!

Tell me if this sounds familiar…

Yes, you know that pivots are meant to show aggregations.  Summaries.  Pivots were NOT invented to display thousands of rows of detail data.

But still, sometimes you need to do precisely that. The biz needs its list of customers and how much they’ve been buying, for instance, and all that data is in YOUR Power Pivot model.

And hey, pivots are really the only game in town* for table-shaped display of data.  So, you build one of the monstrosities like the above.

(*OK yeah, you DO know about this thing called DAX Query Tables, but those are seriously a pain to set up.  So, no.  You rule those out before even starting.  Just like me!

So You Do The Flattened Pivot Dance, Right???

In pictorial form…

Detailed Pivot Report Using Flattened Pivot

The Flattened Pivot Option – Found in the PivotTable Dropdown in the Power Pivot Window

Detailed Pivot Report Using Flattened Pivot

Next, You Pile a Whole Bunch of Fields Onto Rows

Turn Off Subtotals In Your Flattened Pivot

Then, on the PivotTable Design Ribbon Tab, You Turn Off All Subtotals

And Voila!  It’s Slow as Heck.

Read the Rest