FAQ

PowerPivot FAQ “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:

  1. 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.
  2. 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.
  3. 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]

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.

101 Responses to FAQ

  1. Jenny Goddard says:

    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)

  2. Jenny Goddard says:

    Really helpful answers, thanks. I had not found the “Slicer Connections” before, so this really solves my problem.

  3. Tony says:

    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.

  4. Trev H says:

    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.

      • Dave Feldman says:

        I’ve been very successful with this and there are two options. If you have the sap netweaver gateway and a recent version of BW then you can export a query using easy query and make it available as an odata feed. i didn’t have that. So I use a reporting services report with the BW Provider to query BW. Then I export a data feed from the SSRS Report and consume that as odata in PowerPivot or Tabular BISM. I find it works best grab the XML and use it as an inline data services document in my environment, but you may be successful using it as a file as long as your SSAS / PowerPivot server can read that file when you do a data refresh. Btw. it is really powerful to have this integration!

    • Jeremy says:

      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.

  5. Jeremy says:

    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.

      • Jeremy says:

        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

  6. SAP BI Consultant says:

    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

  7. David Peers says:

    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.

  8. Barry says:

    Hi,

    How many rows can powerpivot handle?

    regards,
    Barry

  9. Jon Lankford says:

    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.

  10. Agust Eriksson says:

    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?

  11. rwendeln says:

    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

  12. Cory Purkis says:

    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.

  13. Scott Sobel says:

    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.

  14. Jesper Bork says:

    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.

  15. Smithicus says:

    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.

      • Smithicus says:

        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?

  16. Samson says:

    Hi,
    I am really struggling with PowerPivot(PP), and unable to find an answer to my problem. I have searched the net for hours but to no avail.

    I have bought Excel 2010 and installed the PowerPivot. I have an existing excel file in which I want to create a Pivot Table(PT).

    I am getting this error repeatdely” Initiation of the data source failed”.
    I am also unble to refresh my data. If I look at the PP window, the refresh windwon is no available. The refresh button on excel does not refresh the data. If I look at the PP option, and click to see the source of data, I can see the pivit name, but when I click the PT source Button,”The select a table, or range” is Not available. So where do I see my data? In the old version PT, when I click the source data, I can see the range and souce.

    I do not know what I am doing wrong. Please help before I lose my patience and revert to the old PT version 2007.
    What are the correct steps to create a pivot from an existing open file excel?

  17. Willem says:

    Hi,

    Is it possible to build a Slicer with column headers, so if you select a different header your table/chart changes all data. ( example cost/order, sales/order)?

    Thanks

    • Willem says:

      Some more details about my question.

      I have build a report, with all kinds of results by month (Based on a Column that tells me which month, -I hav o actual date column).
      What I do have is 3seperate Year, Month & Week columns.
      My Goal is to build a Slicer, giving the end user the possibility to choose the view of the results by Year, Month or Week.

      Can someone help?!

  18. OtterBA says:

    I want to my end user to type in two dates in order to sum production for a given range. Is there a way thru formula to do so using cubesets and cubevalue?

    Thanks

  19. Sami says:

    Hi There,
    My Question is “Is there any decent way, where i can amend and import data on an existing powerpivot table from multimple excel worksheet?”

    Background: I have started a large project in PowerPivot and got stuck in one silly problem. I believe this could be a FAQ on the section.

    I have a a workbook with say about 36 worksheets [Eg Jan 2008, Feb 2008....Jan 2009, Feb 2009...Jan 2010...Dec 2010] each with same structure [10 Columns] containing 100,000 rows on each sheet.

    Now i am trying to consolidate all of them and trying to import them in a PowerPivot table for yearly analysis but whenever i am trying to link each sheet it is creating a different linked table. How can i accommodate all of them in one single table? You know what i mean to say.

    Yes, i know i can copy each sheet and then use the paste append one after another, but on that case if any of my source data changes then i will not be able to reflect it in the power pivot correctly.

    I can copy all of them in a note pad and can import it but then again i will loose the flexibility of Excel for Changing data.

    So is there any other decent way, where i can amend and import data on an existing power pivot table from multiple excel worksheet?

    I really really look forward for this annoying problem.

    Regards,
    Sami

  20. Miguel says:

    Hi

    I just had a recent meeting with the people from Jet Reports and they are basically offering me a “lower version of the Powerpivot”, in other words I seee that their software is pretty much the same as Powerpivot but you need to create your own cube before playing with the data. I’m a data analyst who loves excel and powerpivot but I want to get something similar to the Powerview (mainly because of the relational picture files that you can related to some data), is there any way that we could get that functionality in Powerpivot? or is that only for Powerview and Pivot viewer?

    Can anybody show me a chart with the differeces between Powerview vs Powerpivot vs Pivotviewer?

    Trying to make a business dessicion on wether to get the MS Package or the JetReports thing.

    Thx in advance!

  21. Miguel says:

    Sami,

    1. Use a date table and link your dates in each sheet to that dates table.
    2. Make a Measure for the fields that you want, taking in consideration that the measure will be taking in consideration all the 999999 sheets (tables).

    Once you have that measure and your dates table you can go from there, otherwise, the append would do the job each and every time.

    best,

  22. Michael Jack says:

    Hi:

    I have created an Income Statement (P&L) in PowerPivot. We have a dimension for U.S. state and I have added the U.S. State dimension as a slicer. Is there a way to batch the report and have PowerPivot create a pdf document for each state. Once the pdf is created it would have 50 pages. Otherwise the users would have the click the slicer 50 times and save it as 50 different files.

    Thanks,
    Michael Jack

  23. Minoo says:

    This site was extremely helpful. I had a question, I was looking to find an answer to. Using ASSL I have created an Offline Cube file. I wish to use this as a source for PowerPivot. Can this be done? I have tried a few things without success. Your help is much appreciated.

    Thanks,
    Minoo

  24. bruno pozuelo says:

    Hi there
    thanks for the site and all the work you’ve done around it
    I did not managed to find out how to send an email to you directly so I hope that comment will not be lost in the middle of all others
    the question I have is pretty simple : starting from a list of days (01/07/2010, 02/01/2010, etc…) and using any of the DAX function I would have imagine that it would have been possible to show the same day last year (in that example 01/07/2009, 02/01/2009,etc,…) while tackling the february 29th thing (some years that day happen, some others don’t)….But so far I have not found it yet. Any idea or tip to share about it ?

    Regards

    Bruno, a great fan of powerpivotpro from Europe

  25. Bruno says:

    Hi there

    Once again I’m calling the 911 of Powerpivot. The more I work on that tool, the more I enjoy it but the more I face sometimes some tricky situations…
    And as you’re the skillest, nicest (and all in all the best) powerpivot site I’ve read so far, let me request your help and knowledge once again.

    This time I’ve tried to draw a pivot chart from powerpivot table. Something simple. As learnt I’ve selected from the powerpivot menu the pivot table report button and then selected “single pivot chart” (hope the translation into english is fine). Doing this, i’m brought back to excel spreadsheet where there is one tab which is added automatically and named as “datas for graph sheet 1″ and where I can see an empty pivot table + the field list from powerpivot, but there is nothing appearing on the tab where the chart should appear…Normally there should have a chart frame appearing with on the right side of the screen the powerpivot fields, but this time : nothing
    I’ve tried this on a laptop 64 bits with of course powerpivot v1 64 bits installed. Once i’ve seen the chart function did not work properly I’ve tested powerpivot on another laptop (32 bits this time) with powerpivot v1 32 bits without any success….
    HAve you ever seen that ? Any idea of what’s going wrong ? the powerpivot version i use is the V1 downloaded a couple of week ago…the fact that the chart
    I do hope you’ll be my powerpivot hero once again

    Thanks in advance and best regards

    • powerpivotpro says:

      I don’t believe I have ever seen this problem myself, Bruno. Sorry, I don’t have any theories :(

      • Bruno says:

        sh**….i keep looking in google but sounds no one has met that trouble before….any advice about who I should ask this ?

        • bruno says:

          anyway i can send you my spreadsheet and see if it works on your laptop ? if it works on yours that will let me think it is a trouble related to system (version of excel or powerpivot)
          cheers

  26. Nikitas says:

    Hello,
    I’d just like to say that I utterly enjoy working on Powerpivot and it made my life so much easier. As a company we’re going to start using it quite a lot. Thank you so much for the info provided and the hard work you’ve been doing so far.
    I do have a question though. If this is not the place to ask it I do apologise.

    As a company we have two huge accounting databases. One has the actual data and the other the budget data, split in various account codes, divisions etc. They do share however a similar ‘date’ field. Powerpivot doesn’t allow me to link the two tables since that field have duplicates. I can’t create a unique ID field to link them since the only thing different between the two are the amounts stored and the SQL table they come from.

    Is there a way to use one single slicer for the two graphs of the two tables? For the time being I have two slicers, one next to the other, linked separately to the two graphs, but they have the exact same values. It doesn’t look nice.

    Thank you so much in advance.
    Regards,
    Nikitas

  27. Zdravko says:

    Hi,

    Just started using PowerPivot and find the product absolutely perfect for BI self-service reporting for our organisation. I would like to know if at all possible to sequence textual descriptors into a pre-defined sequence rather then the default A-Z sequence. This is visually useful for us when graphing events in a business process. For example if my events or dimensions sequence was D, X, A and B. PowerPivot displays the list along the axis in A, B, D, X order.

  28. David says:

    Hi

    I have shown my Fin Director examples of the Web based Dashboard & Reports that can be achieved with Excel 10 / Powerpivot and Sharepoint 2010. However, as an organisation we have previously invested in Business Objects and also have Xcelsius (BO’s Dashboard tool)available

    I have been asked to compare and contrast the 2 products and am wondering whether anyone has either done, or knows where I might find a comparison or, alternatively, has used both products and can share their experiences.

    Many thanks

    David

    • David Churchward says:

      Hi David

      I would be more than happy to help you out with this. Without knowing your business, it’s hard to be specific about what the key benefits are, but in my experience, speed of turnaround, self service and putting business insight into the hands of the key users (probably Excel pros) is critical to a business. These are all aspects that PowerPivot delivers and where traditional BI often fails.

      Having said that, Business Objects (whilst I haven’t used it extensively) can be a good product. It might be the case that PowerPivot serves to compliment what you already have. Due to the relatively low cost of ownership, this could be a viable model. Overtime, it will become apparent what the best solution is for your business.

      If you want to discuss in more detail (thereby avoiding me waffling in this comment section) please give me a shout at david.churchward@azzu.co.uk.

      Thanks
      David

  29. Sri Meda says:

    Hi,

    How can we implement dynamic secuirty in SSAS Tabular (Server version of PowerPivot) using user group rather than username (using USERNAME()) function?

    Thanks in Advance,
    Sri

  30. bruno Pozuelo says:

    Hi there
    Thanks for this astonishing website. i’m a great fan of your work, allways helpful

    Today I come to you with an issue
    On powerpivot, I tranfered first datas (like turnover, so obviously numbers) from a cube built on analysis services.
    (despite using the assistant of import of datas, i had access to the MDX script that has been created by Powerpivot :

    SELECT NON EMPTY { [Measures].[EPRCANHT] } ON COLUMNS FROM [CUBE-RETAIL] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    Once imported in a powerpivot tab, this info is a text.
    I tried of course to turn that into a number through the data type o the ribbon, but it says
    Failled in changing the type of datas from the column

    on error message detail it is shown :

    L’erreur système suivante s’est produite :  Le type ne correspond pas.
    Erreur de base de données PowerPivot : Échec de la conversion du type de données pour [Table : « Requête », Colonne : «  », Valeur : « 183867510.25867417 »].

    —————————-
    Échec du changement de type de données de la colonne.

    ============================
    Pile des appels :
    ============================

    à Microsoft.AnalysisServices.Modeler.Storage.DataModelingSandbox.ExecuteCaptureLogAndProcessResults(OperationType type, Boolean cancellable, Boolean raw)
    à Microsoft.AnalysisServices.Modeler.Storage.DataModelingSandbox.ExecuteAMOCode(OperationType type, OperationCancellability cancellable, AMOCode code)
    à Microsoft.AnalysisServices.Modeler.Storage.DataModelingTable.Update(UpdateOptions updateOptions)
    à Microsoft.AnalysisServices.Modeler.Storage.DataModelingView.ChangeColumnsDataType(IList`1 columns, OleDbType dataType)
    —————————-
    à Microsoft.AnalysisServices.Modeler.Storage.DataModelingView.ChangeColumnsDataType(IList`1 columns, OleDbType dataType)
    à Microsoft.AnalysisServices.Modeler.Controls.TableWidgetGridControl.ChangeColumnsDataType(List`1 columns, BasicDataType dataType)
    à Microsoft.AnalysisServices.Modeler.ClientWindow.DataTypeSelectionChanged(UInt32 newSelectedIndex, String newSelectedString)

    Our internal IT does not know how to fix this

    So question : have you seen that before ? and do you know how to fix this ?

    P.S. : i work with Powerpivot V1 only

    Thanks in advance for your precious help

    Best Regards

  31. Sven says:

    Hi there!

    Im looking to create a diagram in which a certain amount of hours are distributed evently over a span of time. I want to do this for several different projects with several different start and end dates.

    At my disposal I have the project code, which is unique, and the start date and end date of each work phase.

    The only method I have tried that have come close to achieving what Im looking for is pasting in a series of dates manually for each project, something that is obviously not feasble on a larger scale.

    Anyone got a solution for this? Seems to me to be a pretty natural thing to want to do but I havent found much about it on the web so far…

    Another thing, is it possible to to do a Linked Table from a PowerPivot table I am using a Flattened Pivot Table?

    Thanks in advance

    • David Churchward says:

      Hi Sven. Have you seen the recent Gantt Chat posts? This uses start and end dates so it might give you some direction.

      On the linked table to flattened pivot table, I would suggest that you’ve already got the data you need in your model so why would you do this? The simple answer to your question is no. There would be a serious refresh data issue. Happy to help if you want to work through this.

      Cheers
      David

  32. Bruno Pozuelo says:

    Hi the Powerpivotpro team !
    I’m quite obsessed by developping a financial reporting tool (something similar to Hyperion) using Powerpivot for my current job since I discovered (and thanks to you) how powerful powerpivot can be.
    So far, I managed to have some good results with the prototype I developped.

    However I’m trying to improve 2 things :
    - I have trial balances with amounts expressed in several local currencies and must convert them into a third one using a link between 2 tables (trial balance and fx rate). SO far I added up a column in trial balance table with a relatedtable formula that allows to bring back the fx rate to be used and convert local currency into a third one. But I do suspect there is a “measure formula” to implement directly on pivot table that could bring me to the same result without increasing drastically the size of the excel file. Do you have any idea of how to write it ?

    - in my world, profit and losses (P&L) are translated from one currency to another using average rate (monthly average rate actually) while balance sheet is translated at spot rate (rate of the last day of the month). So on a table, I have a trial balance by month (with both P&L and balance sheet accounts). Once created the pivot table with the month as a slicer, it gives me the correct amount for P&L because with a formula I translated the monthly profit and loss accounts for each month with their related monthly fx rate. But it does not work for balance sheet. Indeed, the con of that formula is that it converts balance sheet accounts per month with the spot rate of each month, while my intention is to show only the accounts of the month selected with the slicer and convert that only monthly amounts with their related spor rate Any tip to do that ?

    Thanks a lot in advance for any advice / tricks / tip you might think relevant

    And thanks a lot for existing. You guys rock !!!

    • powerpivotpro says:

      Bruno I have alerted David C about your question. He should be here in 3… 2… 1…

      • pozuelo says:

        LOL…
        i’ve worked on these ideas all day long but still haven’t found a quick fix. But of course, I don’t have your large experience of this product.
        Cheers and have a nice week-end

  33. David Churchward says:

    Hi Bruno

    You’re bang in my back yard. I’m sure we can nail this. I have consolidation and exchange adjustment models so I’m sure we can solve this.

    Are you able to send me your file? If so, please forward to David.churchward@azzu.co.uk. I will gladly take a look and we can progress from there. I think a blog post on this is long overdue.

    Cheers
    David

  34. Hi!
    I’m happy to find needful help here – thanks so far…
    I have a list of entries in a table stating date and time in the same column. I need to group this data by day, week, month etc. I haven’t found any clues on how to achieve this with PowerPivot. Grouping is not done in the same way as in Excel (which handles this well). Have you got any tips…?

    Best regards
    Lars

    • powerpivotpro says:

      Hi Lars. One way to do this is via calculated columns, using YEAR(), MONTH(), etc., and concatenations of those, then adding those fields to rows rather than the original date.

  35. Plamen Mitov says:

    Hello, powerpivotpro,

    I am new to powerpivot and have an issue with importing data from text files.
    I need to use the first row as header, but if all other rows contain numeric data, powerpivot imports blank cell in the first row. When I try to activate the checkbox “Use first row as column header”, I receive the following message: “More Details:Failed to retrieve data from filename#txt. Reason: The expression contains invalid date constant ‘#txt.NoName’.”

    So, please, if there is a work around, would You be that kind to share it?

    Best regards,
    Plamen

    • Plamen Mitov says:

      OK,
      An additional TAB symbol was detected at the end of row 1 (header), which somehow makes the difference.
      Solved.

      Thanks anyway for reading my worthless crap!

      And now another issue emerges – in the preview I see values in the first column of every visible row, and when the import finishes, about 50 000 out of 70 000 rows have an empty first cell. The import is from tab delimited .txt file.

      Any ideas?

  36. David Levy says:

    Hi,
    Hope this is the right place to leave a question. The book is very good, I am reading it again with more time now.
    One question about relationships: If you have for example a situation where you have actual figures and budgetd figures (sales, expenses, etc). Actuals will find a budget as they “lookup” the budget table. However, the problem is that budgets exists for rows with no actual. The result being that the total budget number will be understated by the sum of those rows. Any practical way to deal with this in Powerpivot?

    Regards

    David

  37. Shahzad Hanif says:

    I want to ask about “IF” function, is there any limitation of using IF function in powerpivot window like I m using my account code to have particular value if the condition is true but powerpivot window is not returning any value although the condition is true and partial results are appearing in powerpivot window.

    Regards

    • powerpivotpro says:

      Hi Shahzad. I’d need more detail on the problem in order to answer. IF() works fine – I use it all the time. But there may be something particular about your situation. Maybe mismatched data types?

  38. wfvandijk says:

    Hi Rob,

    I created a report based on your TopN posts and am wondering if there is a way to “shut-off” a slicer when it comes down to a measure calculation.
    I have a TopN of productID whith slicers allowing for country selection and another one for dealer selection, what I would like to do is have a measure show up in my report which would ignore the fact that I have decided to select a specific country and/or dealer, thus creating a row which would show ranking for a specific product as it refers to Dealer, Country and Global…

    I was thinking about applying ALLEXCEPT, however, due to location of dealer information versus sales information, I keep getting lost.

    Any thoughts on where I should start hunting for a solution?

    Thanks,

    Willem

  39. Amanda says:

    Hi PowerpivotPro team!

    First of all, I MUST thank you for all the brilliant tutorials, I’ve learned soooo much from you guys.

    I have a question regarding slicers and cube function. Is it possible to use a reference to a cell whitin cubvalue function for slicers. For example:
    instead
    =CUBEVALUE(“PowerPivot Data”,$D15,E$13,Slicer_Region)
    I would like to use
    =CUBEVALUE(“PowerPivot Data”,$D15,E$13,$A$1)

    where cell A1 contains which slicer I want to use in a formula (in this case Slicer_Region).

    I need that, because I want to make a report with cube functions where you can dinamicly change a slicer you want to use without having to change all formulas in a sheet. Is that possible?

    Thank you very much for all the help and support you guy give us!

  40. Bryan B says:

    Hello,

    I have a sharepoint list that has a people picker and I pulled that list into PowerPivot however when importing into PowerPivot those people picker columns are lost. If I export the list to excel and then use the table it creates will allow for the people picker column to be pulled into PowerPivot. Is there away to get PowerPivot to pull in the list without exporting the list to excel first and keep the people picker columns?

    Thanks for all your help and great website,

    Bryan

  41. Nikolas says:

    Hi PowerpivotPro team,

    Hope you can help me with one problem, I have 2 unrelated tables, CaseID column in both tables, there are CaseIDs which are not in Table1 but are in Table 2 and vice versa, plus some of the case IDs in Table2 there are multiple times. What I need is to have a column in Table1 where I could see if given CaseID in Table1 is also represented somewhere in Table2 CaseID column.

    Thanks, Nikolas

  42. Dave Feldman says:

    PowerPivotPro team.. have a new FAQ for you. As far as I have been able to find, the Excel Data Model (and therefore PowerPivot) is completely non-functional in Excel 2013 unless you have admin rights to your local workstation. I haven’t seen this documented but have recreated it on 20+ users. Any thoughts on getting around this or if this is just a bug in 2013?

    Thanks
    dave

  43. Dan says:

    Did you ever get an answer to this? I have the exact same issue and am looking for a way to do this.

  44. Dan says:

    My last post is regarding a solution to using a spot rate for powerpivot balance sheet. Orginal post Aug. 31st 2012 above.

  45. Qui says:

    Hi PowerPivotPro team,
    I work new to PowerPivot and amazed by its capabilities but one of the shortcoming i see is row level or user based security. I understood that username() DAX function does solve this but couldn’t find much details.
    I am using Oracle as backend and sharepoint + PowerPivot as SelfService BI. If i want to implement user level security with this set-up, how to proceed? which version will work? Appreciate your help.

  46. Andrew says:

    We opened a Support Incident with Microsoft and got the answer on the requiring Admin rights in Excel 2013. You need to use NTRights.exe in an Administrator Command Prompt to grant your user privileges required to use PowerPivot (Power View, GeoFlow, etc.):

    ntrights +r SeIncreaseBasePriorityPrivilege -u DOMAIN\youruser

    You should see: Granting SeIncreaseBasePriorityPrivilege to DOMAIN\youruser … successful
    Error: ***Error*** OpenPolicy -1073741790 means you were not running command prompt as Administrator

    Now do a complete logoff and then log back in again. The Excel 2013 Data Features should now work.

  47. Mathieu says:

    Hi
    I have a file with many calculated fields and some of them don’t calculate.
    Here’s an example:
    I have this formula:
    =if([State]“QC”,0,if(isblank([QST]),if([HasReceipt]=0,0,RELATED(EXP_REP_TVQ[Rate])*[Expense_Less_Tip]*[QST_pourcentage_taxes]),[QST]))
    and the calculation return a blank field. It shouldn’t.
    I do this:
    =if([State]“QC”,0,if(isblank([QST]),if([HasReceipt]=0,0,RELATED(EXP_REP_TVQ[Rate])*[Expense_Less_Tip]*[QST_pourcentage_taxes]),[QST]))+1
    and the calculation works. Then I remove the +1 and the formula is ok.
    Anyone know why?

  48. Mathieu says:

    I’d rather not.
    That bug is not always present. Not often on my PC. More often on people from my team’s pcs…

  49. Jon Pearce says:

    It looks like this is a big deal. Mr Excel just posted that the retail boxed versions of Excel 2013 (not Office 2013 – just Excel 2013) will include PowerPivot – see http://learnmrexcel.wordpress.com/2013/08/16/how-to-buy-power-pivot-for-excel-2013/

    As I understand it, this is the first opportunity for retail buyers to have access to PowerPivot – otherwise it was only available thru Office 365 Pro Plus or volume purchase. Looks like Rob’s Who Moved my PowerPivot Cheese article got some results.

  50. Brian says:

    What is the difference between the Desktop version and the Server version of PowerPivot?

    • powerpivotpro says:

      Server version lets you publish to the web, people can view and interact with workbooks in their browser (or ipads!). Also you can schedule auto refresh.

      Check out the What is PowerPivot page for a little more info.

  51. Ryan S says:

    Not sure if this is the right place to leave a question, but ill give it a shot. When I use the switch function to put measures in a slicer,it doesnt filter other criteria down to those that have values. For example I am showing Gr Hrs by plant, filtered by year from another slicer. When I use the regular measure, it will only show plants that have hours for the given year, but when i use the switch formula, it shows all plants that have ever had hours, with the value being blank. Any way to only show plants that have data for the criteria I select with using my switch function?

    • Chris Gilbert says:

      One possibility: There are Slicer Setting that can be set to enable/disable the “Hide items with no data”, “Visually indicate items with no data”, and/or “Show items with no data last” In the 2013 version, you can either right-click on the slicer or use the SLICER TOOLS menu to open the dialog that lets you modify these settings.

      Caution: There is a performance hit for having these features enabled, as I learned in Rob’s Advanced PowerPivot on-line class in the “Performance” module.

      Another possibility, a slicer can be “connected” (or not) to any combination of Pivot Tables and Pivot Charts (again, in the 2013 version this is called “Report Connections” on the SLICER TOOLS Options Ribbon.

      Finally, you should make certain that the spelling is EXACT in your SWITCH statement, any variation could result in no filtering.

      Hope this helps.
      Chris

  52. Jon Pearce says:

    I need to create multiple copies of the same underlying PowerPivot table using different data sources. Is there a way to create a script (or even a list) of the DAX expressions that I can use to bulk-populate the new PowerPivot models? These models change frequently so it would be cumbersome to create a template, since the template would need to be constantly updates. I’d like to be able to take an existing model, script out all of the DAX measures (and ideally the table connections) and apply it to a new PowerPivot model. An ideas? Thanks

    Jon

  53. Simon says:

    Thank you for an awesome site. I have a question I cannot find an answer to:

    Why are my numbers imported as text?
    I have an excel table of data that contains fields that are definitely numerical values; there is no text in them except the header. Yet every time I create a linked table to PowerPivot is puts the field in as text column with a little warning sign that lets me create a calculated column (using some IFERROR and INT functions) to essentially replicate it as a numerical value or to leave it as text.

    As text, I obviously can’t do any calculations. I don’t really want to create all the calculated columns and am frustrated as I have no idea why it is doing it. Any help appreciated.

  54. I managed to set-up a data connection link in Excel 2010 to a Bex easy query to read BW data. However, ALL records appeared in Excel despite the query restrection that I applied and verified that it is working in BEx! Please advise.

  55. Jon Pearce says:

    I think this answer is NO, but I’ll ask anyway. Is there a way to default a “dimension” to a value other than “All”? In some of our models we want the default value of dimension to a particular value. I believe that this is possible in SSAS multidimensional cubes, but don’t think it’s possible in PowerPivot, is it?

  56. prasad says:

    I have a problem in my model in excel sheet I had used TDIST function with below formula =IFERROR(TDIST(ABS((E8/100))/SQRT(($AU8/$I8)+($AX8/$B8)),($B8+$I8-2),2), 0)
    Syntax of TDIST(-t,df,2) .

    Where as this is not available in DAX while of trying to write use this in calculate measure in DAX can any help me what is the alternate function of TDIST in excel .

    TDIST function is not available in DAX function

    Can any help me on this

    Thanks,
    In advance.

  57. Hi there. quick and dumb question (did not find the answer in the FAQ (which is by the way the best FAQ I have ever seen/used). I am running Excel 2010. My company has unfortunately still not decided to go for sharepoint. I have a powerpivot that gets data both via an oracle connection and from a local excel file.
    Every morning I need to refresh only part of my oracle connected tabs. Is there any way I can do that without hitting “refresh” tab by tab ? Basically is there a way to select some of the tabs and refresh only these ?
    The reason why I am asking is that I’d like to start the refresh and go take a coffee, rather than staying in front of my computer to hit refresh every time one of the tab is updated…

    Thanks

  58. TJ says:

    Love, love, LOVE PowerPivot, The Book, and this site! Thanks to you and MS for literally changing my life. One thing I have yet to figure out, however, is how to make multiple “flatfile” copies of different cuts of my workbook. I know, “what’s the point?” right? Well, we don’t have PowerPivot installed to the masses for the time being and Leadership is requesting a non-Pivoted file (to avoid the inability to refresh, etc etc). I am still leveraging my data models to create my reports but now I want to disconnect from PowerPivot and unload the data embedded in the backend. I used to be able to accomplish this by right clicking on the Pivot-> Pivot Options->Data-> Uncheck the ‘Save Source data with File’ in non-PowerPivot files. I have tried a bunch of different things, copy->PasteSpecial…, Deleting the PowerPivot Data connection manually and via VBA, etc. Any tips? Thanks!

Leave a Reply