I have a relatively small powerpivot model. invoice (430k rows), cost centre (600 rows), customer(54k rows). The tables are narrow, 5-15 rows. Ive noticed that if i have two or more measures and i move them to the row labels as opposed to the default column labels, it becomes VERY slow and unresponsive. modifying the pivot table in any way results in a wait time of many minutes or even timeout errors.

The fact table has simple sums over invoice amt remaining and an invoice count

i construct a pivot table that has cc.group code, cc.group description, cc.cost centre code, cc.cost centre description, cust.code, cust.name, invoice.id

I filter it to a single cost centre and unpaid invoices only. this limits it to 430 rows from the fact table.

I add the invoice amt and invoice count to the measures. this gets processed and the correct numbers show up within a few sec

constructing the above takes about 10-15 sec with most of that time being spent on moving my mouse to select the attributes.

I move the values to the rows. this takes a few min to process or times out completely depending on which cost centre i've filtered it to. It times out after about an hr or so for the data filtered down to 430 fact rows.

Is this a known issue? this is with excel 2013

Hi Jakub,

how do you manage to drag the values from columns to rows? In my viewable window I cannot spot the option to do it for power Pivots.

Would like to try it on my own example, as I've also experienced a considerable slowdown in a Pivot when dragging quite a lot fields into the row section, but didn't move the value fields.

What happens, if you skip 2 fields from your row section an then drag the values over? Is the slowdown factor just as high as before?

Hi Imke,

Have you got two measures or more in your "VALUES" section?

a "values" label should appear in the columns section, you can then drag that into the rows section.

so now instead of your pivot table looking like

group | Sum | Count |
g1 | 54 | 3

it becomes

group
G1 | Sum of Measure | 54
     | Count of Measure | 3

It becomes faster the fewer attributes I have in my table, but i have my requirements i'm coding to :(. The filtered dataset is only 400 or so rows, so i'd think this shouldn't be so computationally intensive.

Hi Jakub,

thanks, I can see it now (woke up finally :-))

This is really interesting. Try the following:

Create 2 pivots: One with the values in cols and one in rows and convert both to formulas.

Now compare both valuedefinitions: The "column"-ones have two reference fields within the table, the "row"-ones only 1.

The member-Definition (Header field of your column section) of the "column"- ones should look quite simple:

CUBEMEMBER("ThisWorkbookDataModel";"[Measures].[Sum]")

The one from your "row-version" will look ... massive...: It contains all full MDX-Definitions of all attributes in your row sections. So there doesn't seem to be any intelligent "translation" of this request towards a redundance-free compilation/execution plan :-(

I have no idea how to solve this.

Workaround suggestions:

1) Create your pivot with as much attributes in the row section as possible (incl. the key attribut(es)) and add the other needed fields left to your pivot by using cubeformulas within an IFERROR-wrapping (CUBEMEMBER with just one reference to the key attribute). Add an additional reference to all these new fields from your cubevalue-fields, just as if they are new filters.  

2) Take the full cubeformula definition that you already have here and reduce all member definitions to their last and only attribute definition. Then reference all these in your cubevalues as described in 1)

Anyone out there who knows how to overcome this in "standard-pivot"?