Grouping Data in Power Pivots

[kersplash]

Hi,

So I have a large amount of data that I currently use Vlookups to merge data and alot of formulas in calculated columns.

I then analyse this data using pivot tables. I am able to group dates and like data easily using the standard pivot tables.

I thought I'd have a go at trying to use Power Pivot instead (I mean it has the word "Power" in it!), hoping that I could reduce my file size and speed up calculations.

My very first hurdle is coming to grips with not being able to group data in a pivot table created using Power Pivot.

While I have discovered how to do date grouping, which is not intuitive, I don't know how to group other data fields together which I would normally do by a right-click on the field.

Can anyone guide me (read - convince me) that going down this Power Pivot (capital Ps people!!) path is going to better in the long run.

Thanks

[xlnitwit]

With great power comes great complexity. You should be adding the lookup table to your data model and creating a relationship between the two tables, rather than grouping data in the pivot table itself.

[kersplash]

With great power comes great complexity.

Just call me grasshopper.

But at the moment I have lay-people using my report to easily group and ungroup at will. I don't want to make it harder to use.

[xlnitwit]

If they can figure out how to group fields in a pivot, surely they can drag fields on/off a report? Or are they completely ad hoc groups?

[kersplash]

Yes on-the-fly grouping is needed.

[xlnitwit]

Then in all honesty I'd stick with regular pivots unless you have a specific need for Power Pivot. You can do it with PP by having the users update a grouping table but it won't be what they are used to.