The Great PowerPivot FAQ

Title

Client/Server

Topic

Purpose

Answered?

Contributor

Topic : A - Getting Started ‎(10)

Where do I get PowerPivot Beta?

Both

A - Getting Started

Information

Yes

Rob Collie

What is the planned release date for PowerPivot?

Both

A - Getting Started

Information

Yes

Rob Collie

Do I need SharePoint 2010 to use the PowerPivot?

Client

A - Getting Started

Information

Yes

Thiago Zavaschi

Is PowerPivot a replacement for Analysis Services (SSAS)?

Server

A - Getting Started

Information

Yes

Marco Russo

What are the top 5 Reasons Why Your Company Should Embrace PowerPivot Over Competing SSBI Products

Both

A - Getting Started

Information

Yes

Derek Comingore

Do I need SQL Server 2008 R2 to use PowerPivot for Excel?

Client

A - Getting Started

Information

Yes

Thiago Zavaschi

Where can I get the PowerPivot for Excel Help file?

Client

A - Getting Started

Information

Yes

Michele Hart

10 things you should know when you use PowerPivot

Client

A - Getting Started

Information

Yes

Kasper de Jonge

PowerPivot CTP3 is expired

Client

A - Getting Started

Information

Yes

Kasper de Jonge

Are there any good PowerPivot books?

Client

A - Getting Started

Information

Yes

Rob Collie

Topic : B - Setup ‎(10)

What version of Office is required to run PowerPivot?

Both

B - Setup

Information

Yes

Rob Collie

Do I have to install 64-bit addin if I am running 64-bit Office, and vice versa?

Client

B - Setup

Information

Yes

Rob Collie

My PowerPivot ribbon does not show up after a successful add-in install. How can I fix that?

Client

B - Setup

Troubleshooting

Yes

Michele Hart

Server deployment models - departmental vs. enterprise?

Server

B - Setup

Information

Yes

Denny Lee

Is there going to be a version compatible with prior versions of Excel?

Client

B - Setup

Information

Yes

Rob Collie

Can Office 2010 be installed side-by side with older versions of Office?

Client

B - Setup

Information

Yes

Rob Collie

How can I use PowerPivot with my Mac?

Client

B - Setup

Information

Yes

Russell Christopher

Can I Disable and Enable the SQL Server PowerPivot for Excel Add-In?

Client

B - Setup

Information

Yes

Derek Comingore

I am one of the lucky people already testing Office 2010 RC. Can I use PowerPivot CTP3?

Client

B - Setup

Information

Yes

Wesley Backelant

What is the OLEDB provider for PowerPivot and where is it installed?

Both

B - Setup

Information

Yes

Rob Collie

Topic : C - Open/Save ‎(8)

The PowerPivot embedded db has become corrupted after save, re-open. Is there a workaround?

Client

C - Open/Save

Troubleshooting

Yes

Rob Collie

What is the limit on PowerPivot file size?

Client

C - Open/Save

Information

Yes

Rob Collie

How much file compression does PowerPivot get?

Client

C - Open/Save

Information

Yes

Rob Collie

Where is the data stored that PowerPivot for Excel collects and uses?

Client

C - Open/Save

Information

Yes

Derek Comingore

Troubleshoot error "The data connection uses windows authentication and user credentials could not be delegated"

Server

C - Open/Save

Troubleshooting

Yes

Vandana Ponnuru

Save your workbooks at cell A1

Client

C - Open/Save

Both

Yes

Dave Wickert

Why doesn't my PowerPivot workbook open anymore? There is no error, but the Excel Splash screen says "Opening In Protected View"

Client

C - Open/Save

Information

Yes

Russell Christopher

Where does PowerPivot store its temp files?

Client

C - Open/Save

Information

Yes

Rob Collie

Topic : D - General UX ‎(15)

Does my filter in the PowerPivot window apply thru to my pivots?

Client

D - General UX

Information

Yes

Rob Collie

Why I see a PivotTable Field List instead of a PowerPivot Task Pane

Client

D - General UX

Troubleshooting

Yes

Marco Russo

Can I rearrange the PowerPivot Field List?

Client

D - General UX

Information

Yes

Bill Jelen

Is there a shortcut key to jump to the PowerPivot window?

Client

D - General UX

Information

Yes

Bill Jelen

After accessing the Field Settings dialog, I can not use the arrow keys to move around the worksheet?

Client

D - General UX

Troubleshooting

Yes

Bill Jelen

Can I print from the PowerPivot window?

Client

D - General UX

Information

Yes

Bill Jelen

Why does Increase Decimal icon do the opposite?

Client

D - General UX

Troubleshooting

Yes

Bill Jelen

Is there a "clear all" button/command in the PowerPivot Window?

Client

D - General UX

Information

Yes

Derek Comingore

Are there multiple PowerPivot windows (workspaces) in a single Excel 2010 file?

Client

D - General UX

Information

Yes

Derek Comingore

I'd like to use PowerPivot as a "poor-man's" cube substitute. Is there a way to restrict measures exposed to users so that only company approved (and single version of the truth) measures are available?

Client

D - General UX

Information

Yes

Colin Banfield

How to hide the columns you don't use from the pivottable

Client

D - General UX

Information

Yes

Kasper de Jonge

The measures I have imported from an Analysis Services cube are not being treated as a number?

Client

D - General UX

Troubleshooting

Yes

Wesley Backelant

Can I add a chart to my existing Pivottable?

Client

D - General UX

Information

Yes

Kasper de Jonge

Can i do datamining with PowerPivot data?

Client

D - General UX

Information

Yes

Kasper de Jonge

How to use sparklines and PowerPivot data

Client

D - General UX

Information

Yes

Kasper de Jonge

Topic : E - Importing Data ‎(19)

Can I import data from other BI tools into PowerPivot?

Client

E - Importing Data

Information

Yes

Marco Russo

Can I use SharePoint lists as a data source?

Client

E - Importing Data

Information

Yes

Rob Collie

Can I use Sql Server Reporting Services reports as data sources?

Client

E - Importing Data

Information

Yes

Rob Collie

Can I use the SSRS data model itself as a data source directly?

Client

E - Importing Data

Information

Yes

Rob Collie

Why can't I import data from Analysis Services 2000?

Client

E - Importing Data

Information

Yes

Dave Wickert

Reusing a dataconnection with multiple imports in PowerPivot

Client

E - Importing Data

Information

Yes

Kasper de Jonge

Can I import data from the same Excel Spreadsheet that I build a PowerPivot model within?

Client

E - Importing Data

Information

Yes

Derek Comingore

Where can I find sample data for testing/demonstrating PowerPivot?

Client

E - Importing Data

Information

Yes

Vidas Matelis

How can I import data from Madison or an ERP system?

Client

E - Importing Data

Information

Yes

Denny Lee

What's with the 2GB Limit for PowerPivot?

Both

E - Importing Data

Information

Yes

Denny Lee

How can I import public data made available via the Internet?

Client

E - Importing Data

Information

Yes

Derek Comingore

How can I optimize resulting Excel file size and memory used for data import?

Client

E - Importing Data

Information

Yes

Marco Russo

I get "The size specified for a binding was too small" error on import.

Client

E - Importing Data

Troubleshooting

Yes

Rob Collie

What's the best way to import SAP BW Data?

Client

E - Importing Data

Information

Yes

Denny Lee

Im getting an error: "Customized cultures cannot be passed by LCID, only by name."

Client

E - Importing Data

Troubleshooting

Felipe Ferreira

PowerPivot doesn't include a comprehensive visual query tool for importing data. Is there one you can recommend?

Client

E - Importing Data

Information

Yes

Colin Banfield

What is an ATOM feed?

Client

E - Importing Data

Information

Yes

Wesley Backelant

My filename is too long to import in PowerPivot

Client

E - Importing Data

Information

Yes

Kasper de Jonge

How many rows can PowerPivot handle?

Client

E - Importing Data

Information

Yes

Rob Collie

Topic : F - Calculations (DAX) ‎(38)

What is DAX?

Client

F - Calculations (DAX)

Information

Yes

Rob Collie

Is the table name required in DAX if I am writing a formula in that same table?

Client

F - Calculations (DAX)

Both

Yes

Rob Collie

The Time intelligence functions does not return results

Client

F - Calculations (DAX)

Troubleshooting

Yes

Kasper de Jonge

How can I calculate a DISTINCT COUNT measure?

Client

F - Calculations (DAX)

Information

Yes

Marco Russo

How to calculate Standard Deviation in PowerPivot?

Client

F - Calculations (DAX)

Information

Yes

Rob Collie

How to handle parent-child hierarchies with DAX?

Client

F - Calculations (DAX)

Both

Yes

Karen Aleksanyan

How to slice data with many-to-many relationship?

Client

F - Calculations (DAX)

Both

Yes

Karen Aleksanyan

Introduction to DAX time intelligent functions in PowerPivot

Client

F - Calculations (DAX)

Information

Yes

Kasper de Jonge

Does DAX support the Excel TEXT function?

Client

F - Calculations (DAX)

Information

Yes

Bill Jelen

Converting dates to day of week

Client

F - Calculations (DAX)

Information

Yes

Bill Jelen

Can I calculate a field in Table1 times a field in Table2?

Client

F - Calculations (DAX)

Information

Yes

Bill Jelen

DAX Measure to Calculate % of Total Sales?

Client

F - Calculations (DAX)

Information

Yes

Bill Jelen

DAX Measure to calculate % of today's sales?

Client

F - Calculations (DAX)

Information

Yes

Bill Jelen

DAX Measure to calculate MTD Sales For This Region

Client

F - Calculations (DAX)

Information

Yes

Bill Jelen

Get the sum of values of the last month having data using LASTNONBLANK

Client

F - Calculations (DAX)

Information

Yes

Kasper de Jonge

How can I do ABC Analysis (Pareto) with PowerPivot?

Client

F - Calculations (DAX)

Information

Yes

Marco Russo

How do I create a running total that spans over multiple years and months?

Client

F - Calculations (DAX)

Information

Yes

Colin Banfield

How do I group numbers in a PowerPivot PivotTable like I can in an Excel PivotTable?

Client

F - Calculations (DAX)

Information

Yes

Colin Banfield

I have a date field in my sales table. How can I use the field to group by fiscal year and quarter in my PivotTable?

Client

F - Calculations (DAX)

Information

Yes

Colin Banfield

How can I create a moving average calculated measure?

Client

F - Calculations (DAX)

Information

Yes

Colin Banfield

How do I deal with semi-additive measures using DAX?

Client

F - Calculations (DAX)

Information

Yes

Russell Christopher

I have sequential data in my table but no date or partial date field. Is there an equivalent to some of the Time Intelligence functions to allow me to create appropriate measures?

Client

F - Calculations (DAX)

Information

Yes

Colin Banfield

In my DAX measure, aggregations over some related tables are acting upon the ENTIRE table, unfiltered by Pivot context. Why?

Client

F - Calculations (DAX)

Information

Yes

Rob Collie

I have a measure called SalesAmount. Can I use "Sum of SalesAmount" in a calculated measure?

Client

F - Calculations (DAX)

Information

Yes

Colin Banfield

Is there a way to create a new calculated measure without having to switch bewteen the PivotTable Tools tabs and the PowerPivot tab?

Client

F - Calculations (DAX)

Information

Yes

Colin Banfield

How can I create an arbitrary time period for use with the Time Intelligence functions?

Client

F - Calculations (DAX)

Information

Yes

Colin Banfield

How do I create a Rank calculated measure?

Client

F - Calculations (DAX)

Information

Yes

Colin Banfield

Time intelligent PowerPivot functions and fiscal year

Client

F - Calculations (DAX)

Information

Yes

Kasper de Jonge

How can I calculate difference in days, hours, minutes and seconds between two dates

Client

F - Calculations (DAX)

Information

Yes

Vidas Matelis

My time intelligent function doesn't filter, show data on rows or always show the total sum

Client

F - Calculations (DAX)

Information

Yes

Kasper de Jonge

Is there a recommended text editor for creating and editing formulas? I create complex measures that are difficult to read and troubleshoot.

Client

F - Calculations (DAX)

Both

Yes

Colin Banfield

Client

F - Calculations (DAX)

Information

Yes

Kasper de Jonge

Using the NOW() Function gives a old datetime value

Client

F - Calculations (DAX)

Information

Yes

Kasper de Jonge

So i added a measure in my Pivottable,is this is a client side calculation?

Client

F - Calculations (DAX)

Information

Yes

Kasper de Jonge

What does the "Formula is invalid" error mean when adding a PowerPivot measure?

Client

F - Calculations (DAX)

Troubleshooting

Yes

Rob Collie

Get the YTD of same period last year using DAX

Client

F - Calculations (DAX)

Information

Yes

Kasper de Jonge

Simple use of the "Earlier" DAX Function

Client

F - Calculations (DAX)

Information

Yes

Rob Collie

Shopping Basket Analysis

Client

F - Calculations (DAX)

Troubleshooting

Dave Boylan

Topic : G - Relationships ‎(5)

Can I have multiple relationships between two tables?

Client

G - Relationships

Information

Yes

Rob Collie

Are multi-column relationships supported?

Client

G - Relationships

Information

Yes

Rob Collie

Are many-to-many relationships supported?

Client

G - Relationships

Information

Yes

Marco Russo

Does renaming a table in the PowerPivot window break existing relationships?

Client

G - Relationships

Information

Yes

Derek Comingore

How to disable automatic relationship detection

Client

G - Relationships

Information

Yes

Kasper de Jonge

Topic : H - Pivot Tables ‎(21)

Is drillthru supported?

Client

H - Pivot Tables

Information

Yes

Rob Collie

When I add a field to the Values area of my pivot, I get a blank pivot. What's going on?

Client

H - Pivot Tables

Troubleshooting

Yes

Rob Collie

In my pivots, the Group button is disabled. Why?

Client

H - Pivot Tables

Troubleshooting

Yes

Rob Collie

Name 5 things I can do with PowerPivot that I can't do with Excel PivotTables alone.

Client

H - Pivot Tables

Information

Yes

Rob Collie

The multi-pivot reports are cool, but can I modify them? Ex: Remove some pivots from slicer filtering? Or use two PivotTables, which is not an offered option?

Client

H - Pivot Tables

Information

Yes

Rob Collie

How can I arrange two or more measures in the Values area?

Client

H - Pivot Tables

Information

Yes

Bill Jelen

Will my row/column fields sort into Custom List sequence?

Client

H - Pivot Tables

Troubleshooting

Yes

Bill Jelen

My measures always SUM. Why can't I choose Average, Min, Max...

Client

H - Pivot Tables

Information

Yes

Bill Jelen

Why is Calculated Field greyed out?

Client

H - Pivot Tables

Information

Yes

Bill Jelen

I hate the name "Sum of Sales". Can I change it?

Client

H - Pivot Tables

Information

Yes

Bill Jelen

How can I get rid of "Row Labels" label in the pivot table?

Client

H - Pivot Tables

Information

Yes

Bill Jelen

I hate the Compact Layout. Can I default to something else?

Client

H - Pivot Tables

Information

Yes

Bill Jelen

Why is Show Report Filter Pages greyed out?

Client

H - Pivot Tables

Information

Yes

Bill Jelen

How do I permanently change the numeric format in my pivot table?

Client

H - Pivot Tables

Information

Yes

Bill Jelen

Are PivotTables and PivotCharts the only type of report I can build? What are "Cube Formulas?"

Client

H - Pivot Tables

Information

Yes

Rob Collie

How can I change my summarization from the default Sum to Average, Count etc.? The Summarize Value By option on the Ribbon is disabled.

Client

H - Pivot Tables

Information

Yes

Colin Banfield

How can I automatically sort my Month or YearMonth field chronologically in a PowerPivot PivotTable? Excel always sorts fields placed in the Row and Column areas alphabetically.

Client

H - Pivot Tables

Information

Yes

Colin Banfield

How can I create a field hierarchy that I can drag to the PivotTable?

Client

H - Pivot Tables

Information

Yes

Colin Banfield

Can I have more than 1 filter per field in a pivot?

Client

H - Pivot Tables

Information

Yes

Rob Collie

What does the "Element not found" error mean in a PowerPivot pivottable?

Client

H - Pivot Tables

Troubleshooting

Yes

Rob Collie

What does the "Command was canceled" error mean in a PowerPivot pivottable?

Client

H - Pivot Tables

Troubleshooting

Yes

Rob Collie

Topic : I - Publishing on SharePoint ‎(9)

I can't upload PowerPivot files from Excel to SharePoint. I am getting an error.

Client

I - Publishing on SharePoint

Troubleshooting

Yes

Denny Lee

How to upload a PowerPivot App to SharePoint in CTP

Client

I - Publishing on SharePoint

Information

Yes

Dave Wickert

Why does Excel Services tell me there were unsupported features in my workbook?

Server

I - Publishing on SharePoint

Both

Yes

Lee Graber

How to make your PowerPivot sheet look great

Client

I - Publishing on SharePoint

Information

Yes

Kasper de Jonge

When I look at the PowerPivot Management Dashboard Query Response Times chart, what does Trivial, Quick, ..., Exceeded mean?

Server

I - Publishing on SharePoint

Information

Yes

Denny Lee

How do I keep a workbook ‘pinned’ in memory

Server

I - Publishing on SharePoint

Information

Yes

Dave Wickert

Can i protect my PowerPivot sheet to "view only"

Server

I - Publishing on SharePoint

Information

Yes

Kasper de Jonge

Configure SharePoint 2010 to work with large PowerPivot files

Server

I - Publishing on SharePoint

Information

Yes

Dave Wickert

How much does PowerPivot for SharePoint cost?

Server

I - Publishing on SharePoint

Information

Yes

Rob Collie

Topic : J - Report Gallery ‎(6)

Manage the sort order of your PowerPivot sheets in the SharePoint Gallery

Both

J - Report Gallery

Information

Yes

Kasper de Jonge

PowerPivot workbook not showing up in SharePoint PowerPivot Gallery

Server

J - Report Gallery

Troubleshooting

Yes

Kasper de Jonge

So i requested a PowerPivot workbook from SharePoint, what happens?

Server

J - Report Gallery

Information

Yes

Kasper de Jonge

Can I force the snapshot (thumbnails) for my PowerPivot workbook to refresh?

Server

J - Report Gallery

Information

Yes

Russell Christopher

Why document snapshot is not visible in Report Gallery?

Server

J - Report Gallery

Troubleshooting

Yes

Marco Russo

Use a PowerPivot workbook in a Excel Web Access Webpart

Server

J - Report Gallery

Information

Yes

Kasper de Jonge

Topic : K - Data Refresh ‎(5)

Can I run the ACE provider on the server?

Server

K - Data Refresh

Information

Yes

Dave Wickert

When I do a "Refresh All" in Excel, my PowerPivot data is still old. What am I doing wrong?

Client

K - Data Refresh

Troubleshooting

Yes

Rob Collie

Does PowerPivot support incremental refresh from its sources or is it always a full refresh?

Both

K - Data Refresh

Information

Yes

Rob Collie

Where in SharePoint can I define "Business Hours" for PowerPivot data refresh?

Server

K - Data Refresh

Information

Yes

Vidas Matelis

Can i configure an schedule for automatic Data refresh more than once a day?

Server

K - Data Refresh

Information

Yes

Jochen Juelke

Topic : L - Security ‎(1)

What is the security model for PowerPivot?

Server

L - Security

Information

Yes

Rob Collie

Topic : M - Traditional OLAP ‎(6)

Are "many to many" relationships supported in PowerPivot?

Client

M - Traditional OLAP

Information

Yes

Marco Russo

Are "parent child dimensions" supported in PowerPivot?

Client

M - Traditional OLAP

Information

Yes

Rob Collie

Can I run the PowerPivot version of Analysis Services and the traditional MOLAP version of Analysis Services on the same server?

Server

M - Traditional OLAP

Information

Yes

Rob Collie

1 - 150

PowerPivotPro's FAQ Site

The Great PowerPivot FAQ

Quick Launch