“But I still haven’t found what I’m looking for.”
-Bono
“In answer to your query, they’re written down for me.”
-Blade Runner Holden
We’re fortunate to already have a number of great sources of PowerPivot information – the official site, some great blogs, and several forums.
We now find ourselves with a familiar problem, though: given the breadth of the PowerPivot product, finding the answer to a specific question is often difficult unless you’ve been following all of those sources since their inception. Digging through archives isn’t a lot of fun, even when assisted by a search engine.
So here it is…
Click Here to Visit the Great PowerPivot FAQ
Contributing to the FAQ
Most of the q’s in the FAQ as of today came from a list I’d been maintaining in Excel, and I’ll of course be adding to it over time, but I hope to not be the only one responsible for all of this :)
So if you want to contribute, here are the three ways to do so:
- You can email me a question and answer (or post it in comments). If I agree that it qualifies for the FAQ, I will post it to the FAQ and credit you as the contributor.
- You can send me an answer to a currently unanswered question, and again I’ll credit you. Notice there’s an “Answered?” column in the FAQ, and there are a few “No’s” in there.
- I’m also hoping to have a few co-moderators who have edit rights. I have a few people in mind and will be contacting them directly. Depending on response, I might open it up to volunteers.
I don’t have enough logins to go around, and certainly don’t want to open anonymous editing, so for now that’s gonna have to be the system.
New Features of the FAQ
Have you ever noticed the View dropdown? It contains 3 items of note:
![View Dropdown_thumb[6]](/Users/Rob/AppData/Local/Temp/WindowsLiveWriter1286139640/supfilesE400DE/View Dropdown[8].png "View Dropdown_thumb[6]")
Latest View
This view shows you the most recently updated FAQ items at the top. Useful if you just want to see what’s new on a particular day.
Portuguese Translation View
Yes, this is real :) Thiago Zavaschi has been translating FAQ content from English to Portuguese, to satisfy the hungry PowerPivot audience in Brazil :)
If you see a need for another language translation and would like to volunteer, please let me know. It’s very easy for me to set up another view.
Unanswered Questions View
I know, hard to believe, but there actually ARE a few questions posted that have no answers yet.
If you see a question here that you know the answer to, please drop me or any of the moderators a note. We will credit you with the answer :)
Why SharePoint?
I chose to use SharePoint because it gives me a convenient publishing mechanism – I get a hyperlinked table of contents for free, without manually having to keep that up to date in HTML. It also gives readers the ability to sort and filter, and I can annotate with additional columns as needed. I can use that site to publish all kinds of other stuff, too – other lists, wikis, whatever. (If you have suggestions let me know.)
Also, the Data Grid view let me directly copy/paste my existing list of questions and answers from Excel directly into SharePoint.
And all hosted for $9 a month (as long as I’m ok with anonymous access, which I am). Not bad.
Hi. Thanks for the great site, it has been lots of help! I have a few questions and I’m not sure if they are worthy of the FAQ, but I thought I’d give them a try, as I have not yet come up with answers.
1 – How can you get away from the set display formats (two charts, one chart and a pivot etc)? If you add new charts/pivots into the sheet can they be linked to the existing slicers?
2 – Can you disassociate a chart from a slicer. For example, 2 charts with 2 slicers and I want the 2nd chart to only change with the 1st slicer (there is some logic to this even if it sounds strange).
3 – Is there a good DAX solution for variences between data within a table. For example, a table of financial data with actual and budget data can easily produce a pivot table of account type vs Actual/Budget but I have been unable to create a varience as a measure without adding extra calculated columns.
Thanks, Jenny Goddard (Blueprint, UK)
Hi Jenny, thanks for the kinds words :) Glad the site is helpful.
1 and 2 – definitely FAQ-worthy. I have posted the start of an answer on the FAQ site. Will research a little bit to see what kinds of hacks are possible if any.
3 – sure sounds like something we can do with a measure. Are you doing something like SUM([Actual]) – Sum([Budget])? Without seeing more detail on your scenario, my blind guess is that perhaps you are using “naked” columns in your measure, which is a syntax error. Measures deal only in aggregate functions. See the powerpivotpro channel on youtube for some how-to’s on measures http://www.youtube.com/powerpivotpro
OK, updated the FAQ item with my findings from exploring the unsupported scenarios.
http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=57
Really helpful answers, thanks. I had not found the “Slicer Connections” before, so this really solves my problem.
You are quite welcomes Jenny. Thanks for posting the question :)
Hi Rob,
Congrats on the site and on PowerPivot itself.
Is DateDiff type functionality available within PowerPivot?
Some uses I have in mind include calculating elapsed times from dates of birth and from dates of referral to a service.
Thanks,
Tony.
Hi Tony. Thanks, for both compliments!
Check out the DAX section on the FAQ. There are a number of topics there dealing with time intelligence. If you don’t see the answer to your question there, please let me know :)
http://powerpivotfaq.com/Lists/TGPPF/AllItems.aspx?View={012CA162-6911-49E5-99CD-8D47B14D102A}&FilterField1=Topic&FilterValue1=F%20%2D%20Calculations%20%28DAX%29
Hi
What is the best way to bring in SAP BW data. I heard the best way was to bring it in via a reporting services report. I have two problems with that, the first is that we dont have SQL 2008 RS only SQL 2005. The second is that it seems nuts to have to build a reporting services report everytime we want to bring in new BW data. Are there no drivers that we can use. I suppose we could export the BW data into a text file and then powerpivot that but it would be great to connect directly to the BW query and play with the data. Thanks
I don’t personally have the answer for you yet, but I have seen the question quite a bit now. I have posted it as an unanswered question.
And this also prompted me to post an update letting everyone know about the new Unanswered Questions View.
If this is possible this could really be a game changer for me! So many possibilities of creating awesome dynamic dashboards without having to run a BW report first and then importing it into excel/powerpivot. Take away that whole step and have it be automatic will give me tons more time to actually create cool stuff instead of always running lots of canned reports everyday.
I will see if I can flush someone out, either at MS or on Twitter, who has done this, and get them to answer the FAQ.
Tx! I still won’t be able to use this stuff anytime soon but I love learning new stuff especially when it deals with excel. Microsoft is going to have to push these services hard to big corporations as I know my company won’t be upgrading to Sharepoint 2010 anytime soon.
Would it be useful/possible to deploy a single SharePoint 2010 server? Because I don’t think we require the whole company to upgrade the SharePoint backone.
That I don’t know the answer for. I do not work in our IT dept. I am just a Analyst for a Brand inside our company. I am one of a kind where the other brands don’t have a “me”. I get to fiddle around and create reports based off of SAP BW data. But nothing dynamic like this. My company has been rolling out sharepoint across the company the past year and it still kinda in its infancy for us. I am all for this stuff and I can’t wait to be able to learn more about how to use this! I have it on my home pc but the ability to link it via excel services is where I feel this will shine.. Keep up the good work and keep the blog posts coming along!
This is the email I got a while back from our Sharepoint developer.. I already told him just the developer needs the Office 2010/PowerPivot. But knowing us it will still be a few years.. :(
I agree that this could be a powerful tool for enabling users to build some of their own reporting functionality. However, there are a few hurdles to overcome before it can even be seriously considered:
• PowerPivot/Gemini is an Excel 2010 plugin. Office 2010 is still in Beta, and we (WWW) are barely getting onto 2007 over 3 years after release. I am not aware of a WWW 2010 upgrade plan (but that doesn’t mean there isn’t one).
• This functionality relies on Excel Services on a SharePoint 2010 platform
o SharePoint 2010 is currently in Beta and a release date hasn’t even been announced; it is unknown if/when we will upgrade
o Excel Services is an Enterprise licensed feature, meaning that each user that employs the functionality (or has access to a site that has it enabled) must have an additional client access license – current pricing is about $100 per CAL in SharePoint 2007, but pricing hasn’t been announced for 2010 yet
Hi Jeremy, hi Rob,
I had the same idea like Jeremy to test the retrieval of data which is provided by SAP BW. No surprise that I also wanted to check this – I am a SAP BI consultant ;-) Don´t worry Rob, I am not an employee of SAP, I am only an independent, self-employed SAP BI consultant. Due to the currently enormous changes/challenges (and the incredibly long lasting confusion) of SAP in the area of the BI frontend tools (BO Tools, new developments such as the new Pioneer frontend which btw partly looks pretty similar to Power Pivot) I am more and more looking for alternatives – out of frustration with the chaotic product strategy. Therefore I had a look on PowerPivot which looks pretty nice, but I also missed the possibility to access the SAP BW. Which is a bit surprising, since SAP BW is increadibly widespread in many big corporations nowadays.
Having checked the Connection a bit more in depth there is good news and bad news.
The good news at first:
There is indeed already a possibility to connect to a SAP BW query. Just go to the button “From Database”, then choose the entry “Others (OLE DB/ODBC)”. A window will open up and there you can see the button “Create”. Hitting this button, another window will open up and there you will see four different tabs. You will start on the second tab which is named “Connection”. But you need to go to the first tab which is named “Provider”. Doing this you will see a list of many different OLE DB/ODBC drivers. If you scroll down, you will see the entry for “SAP BW OLE DB driver”. This is in general one possibilty to connect to the SAP BW system. Of course it presumes that your SAP BW query has been flagged as SAP OLE DB query – which is not the standard when a new SAP BW query is built (not necessary). It is just a flag in the SAP BEx Query designer. If you want to go for this solution, then I recommend to copy an existing query, go to the change mode of the query designer and set this flag. This is all you need to do and then you can use this query for PowerPivot as a dataprovider.
So, up to here everything looks pretty good. Once you chose this entry SAP BW OLE DB provider, you need to to go to the second tab (Connection) and then you go to point 3. Since I am using the German version of PowerPoint I will not even try to translate this point 3. I guess in English it will be named differently from what I believe it might be translated correctly. Anyway, here you choose the drop down box and immediately the famous SAP Logon Screen will pop up, where you need to enter your SAP BW user name and password. Having done this it takes a few seconds and the same drop down box will open up again and offer you all those BW queries which are flagged as OLE DB queries.
From here you should be only a few seconds away from being happy.
But as always in life, there is not only light, but also some shadow. :-(
Now the bad news comes:
Everything works fine up to here. Even if you choose one of the OLE DB capable SAP BW queries, it still looks fine. You confirm then with OK and then PowerPivot will start to work… and work… and work. Then – after some time – the window will freeze and… Shit, Excel will crash. I tried it several times, but it does not work – or let me say: It works all the times very similar – it crashes.
Summarizing all this, it means: Already today PowerPivot theoretically has built in the ability to access the SAP BW by accessing SAP BW queries. The precondition is that those people who are SAP BW query designers in your company make a copy of the query for you, flag it as OLE DB capable query and transport this to the production system. Then nearly everything you need is provided. The only limitation right now is that Microsoft needs to find out, why the PowerPivot including Excel crashes… ;-) Once they found the bug, the solution should work… in theory at least… ;-)
There is currently no possibility to access the SAP BW cubes directly which is really a very pity, because since Release 7.0 there are quite a few different possibilities also to access the cubes directly with 3rd party tools. One possibility is the XMLA interface which originally was developed by Microsoft by the way… :-) and which works fine. We just tried it with another 3rd party tool.
Even if I cannot provide you a working solution right now, I hope that I at least could make clear that there is a potential way to directly access SAP BW data, in case that Microsoft finds out why its product currently still crashes when using the SAP BW OLE DB provider.
Best regards,
Joachim
Thanks, this blog helped me in solving some problems with the latest version, Why do they always leave out vital documentation when they release a new version? It may be trivial to them but not to me. I’m sure we’re not alone either.
Hi,
How many rows can powerpivot handle?
regards,
Barry
Now answered. http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=150
:)
Hi!
I am struggling with a couple of expressions in PowerPivot. 1). I am in need of an expression that accumulates the values over a period of multiple years. 2). I am in need of an expression that uses the last accumulated value for a time period.
1). I am drawing an S Curve for cost over time. The table consists of two columns (Performance Date and Budgeted Cost). Each row contains a date from 1/1/2010 through 6/1/2012 and a cost for each date. I need the expression to accumulate the values from one date to the next for the entire duration.
Table:
1/1/2010 $10.00
2/1/2010 $20.00
3/1/2010 $30.00
Desired Results:
1/1/2010 $10.00
2/1/2010 $30.00
3/1/2010 $60.00
I am currently using the following expression but it only works for the first two years: =TOTALYTD(SUM(Budgeting[Budgeted Cost]),Budgeting[Performance Date],all(Budgeting)) + CALCULATE(SUM(Budgeting[Budgeted Cost]),PREVIOUSYEAR(Budgeting[Performance Date]),all(Budgeting))
2). I have accumulated values for a date range (i.e. 2/1/2010 – 12/1/2011) and now need to create an expression to use the accumulated value for the last date to calculate another value.
Table:
1/1/2010 $10.00
2/1/2010 $20.00
3/1/2010 $30.00
Expression: =TOTALYTD(SUM(Budgeting[Budgeted Cost]),Budgeting[Performance Date],all(Budgeting))
Results:
1/1/2010 $10.00
2/1/2010 $30.00
3/1/2010 $60.00
I now need syntax to use the $60.00 value (last accumulated value) in another expression.
Any help that you can provide will be greatly appreciated!
Regards,
Jon Lankford
Hi Jon!
First recommendation: when working with date-related logic in PowerPivot, it is ALWAYS best to start with a Date/Calendar table that is separate from your main table.
Now for question #1. Assuming a table named Sales as your main table, a table named Dates as your date table, and a pre-created measure named [SalesQty], the following works great in my limited experimentation:
[SalesQty](DATESBETWEEN(Dates[Date],1/1/1900,LASTDATE(Dates[Date])))
Loosely translated, that means “take the SalesQty measure and evaluate it over all dates between 1/1/1900 and the Last Date you find that corresponds to the filter context of the current pivot cell.”
This was a useful excercise for me since at Pivotstream we often cannot use these functions as-is, so it’s easy for me to get a bit rusty with them (plus their behavior has changed since the Beta). Looking at #2 next.
-rob
#2: Define a new measure, name it something like “Running Sum on Last Date”
The formula goes something like:
[Original Running Total Measure From Part 1](LASTDATE(ALL(Dates[Date])))
First it take the Date column, reverts it to ALL (removes current filter context from the pivot cell).
Then it finds the last date out of all of those dates.
Then it evaluates your original measure against that date.
Now you can reference your NEW measure in other measures.
check out: http://www.powerpivotblog.nl/accumulate-values-running-value-over-multiple-years-in-powerpivot-using-dax
Hi, I’m having problems connecting PowerPivot with an Oracle database. I’ve installed the latest oracle 64 bit client (my machine is W7 64 bit with Office 64 bit running). The error message I get is “The ‘MSDAORA’ provider is not registered on the local machine.” Have you guys run into this issue?
I’m having the same problem. Were you able to resolve?
I’ve been working with PowerPivot for a couple of months now; great tool. So I know how to get data into a PowerPivot table.
Can you export a data set, however, from PowerPivot back into Excel? I don’t see an export function in PowerPivot. When I do a “copy and paste” of a PowerPivot table back to an Excel worksheet, I get errors in the data set (especially if it is a large table or I have a filter on the table).
Thanks for the advice.
Ron
Can I add a total to the horizontal axis of a stacked chart? I have the total in my underlying pivot (created by PowerPivot) but do not see a way to expose that as a bar on my chart.
Example:
Horizontal Axis has “Subregion Name” showing % sales attainment. Additionally I want a bar showing how the Region is doing overall.
Hi Rob & Jeff,
I enjoyed your recent webex you guys did on Wednesday. I was the one that asked if a slicer can be prepared that lists the columns in a table, from which you pick, then another slicer would respond to that and then list the individual values in the column you picked from the previous slicer… you responded that yes that can be done… I’d like to follow up – how? Is there a brief example yuo could send me that I could look at as a model? Is there an easy explanation of how this could be done? I’m really interested in this kind of functionality to make my life much easier and productive at work.
Thank you very much in advance.
Hi Scott, thanks for the kind words on the webinar :)
I’m afraid I may have misunderstood your question though. I know how you can show a completely different set of measures in response to a slicer click, but completely changing the contents of one slicer based on selections made in another is not possible. Slicer contents themselves are very much static.
There is, however, a way to have the contents of slicer B change sort order, and have certain values “bubble to the top” in response to another slicer click. That’s just the default behavior known as cross filtering though, and if you’ve played with slicers at all, you’ve seen that in action already (but it did NOT show up in my demos on Wednesday).
Let me know if you need some pointers on that. Searching this blog for “cross-filtering” should turn up a number of past posts.
Hi there,
I can’t seem to get RSS feeds working as a data source in PowerPivot in Excel 2010 – e.g. the following currency exchange rates RSS feed which works fine when pasted into IE8:
http://www.nationalbanken.dk/dndk/valuta.nsf/valutakurser.rss?OpenAgent&EUR,USD
Hope that someone can enlighten me on using RSS feeds as PowerPivot data source.
Can I set up a data refresh that runs at least every 15 minutes every day? (on PowerPivot v1)
Thanks for your time,
Ian
No, you cannot. But we have software at Pivotstream that enables that scenario.
Thanks for your respons, it seems that frequent refreshes require the Powerpivot workbook to be published to SSAS denali (and then use a sheduled job to ‘process’ the tabular cube).
Which means upgrading to Denali first. Damn Microsoft, always leaving you wanting more… ;)
That which you use at Pivotstream, is that a seperate installable package?