I figured it was time for an updated 10 things you should know when you use PowerPivot. The last one is from January 2010 when the product wasn’t even released.
So what do you need to know when you want to start with PowerPivot:
- You need Office 2010, with the free PowerPivot addin installed.
- Where do I create my calculations? In the pivottable or in the PowerPivot window?
- Understand PowerPivot pivot table context
- Understand relationships in PowerPivot
- Know the golden rules when working with Time Intelligent functions
- One I cannot emphasize enough although it seems not so important: When you add new data from an existing datasource make sure you reuse your connection.
- How CALCULATE works in DAX
- Think about what you import, do not import columns you never will use (like a PK of the fact table), try to avoid unique distinct rows. This will make you PowerPivot model much lighter to work with.
- Hide the PowerPivot columns you don’t want to use, like keys and dates. This will make your pivottable experience much easier.
- For the IT pro: When working with PowerPivot and SharePoint check out these video’s
Also check out:
A question on the PowerPivot for Excel forum piked my interest: Cannot Accumulate Values Over Multiple Years. What Jon here was trying to do was do a a running values over multiple years. Running values over a single year are easy using the TotalYTD value. But can we do this over multiple years?
The first thing i tried to use was SUMX where i want to calculate the YTD for each year:
=SUMX(all(dimdate[caledaryear]),TOTALYTD(sum(FactSales[SalesAmount]), DimDate[Datekey])
But this gives me the same result as a regular YTD does. And this is because SUMX responds to pivot context just like anything else. For a moving average over a custom time span we need to make a function of our own. To get the result we want we need to change the context to not only include the current period but also all periods leading up to this period. We can use the DAX CALCULATE function to change the context of our measure and the DATESBETWEEN to get the date range.
This results in the following function:
=if(sum(FactSales[SalesAmount]) > 0,
Calculate(sum(FactSales[SalesAmount]),
DATESBETWEEN(DimDate[Datekey],FIRSTDATE(all(FactSales[DateKey])),
LASTDATE(FactSales[DateKey])))
,Blank())
The function works as follows: Calculate the sum of FactSales[SalesAmount] of all the dates between the first date of all the sales using the all(factsales[datekey]) to the lastdate in Pivottable context. If you put month on rows the current context is all the dates in the months that is selected, lastdate gives us the last day in the month on that row.
You could also set a hard start date by replacing the start date with DATE(2010,1,1) for a start date of 1/1/2010.
This is the result:
I recently ran across a SharePoint issue that prevented the generation of a thumbnail of workbooks in my PowerPivot gallery. First of all i use the blog post of Dave Wickert Troubleshooting Gallery specific issues to debug my problems. Using this I found a solution to the following problem:
In a few installations where I created a new web application using a hostheader, the PowerPivot thumbnails turned into red crosses.In my case the reason for this was that the GetSnapshot.exe is unable to reach the SharePoint gallery. In all these cases the Site was unreachable from within IE on the server console as well. Disabling the loopback check in SharePoint (http://support.microsoft.com/default.aspx?scid=kb;en-us;896861) resolved the issue and the Snaphots are generated ok again.
I recently had a problem that needed The 5-point palm, exploding fxn technique as described by Rob in his PowerPivotPro blog post. I have used SUMX and COUNTX with success a few times before but this time I had a hard time getting my head around my current problem.
While talking with Rob about it I realized I was thinking way to difficult. In this blog post I will try to make more sense of the X functions and give you a little help in building you own X function.
Read more…
I came across a great blog post that shows how to import two excel sheets that have a similar column structure into one PowerPivot Table:
http://blog.contextures.com/archives/2010/09/06/powerpivot-from-identical-excel-files/
Very nice!
Just a quick one tip today, I thought I already blogged this one but i couldn’t find it.
Sometimes you want your value to be always x chars wide. For example you want to create a yearmonth column of always 6 figures, 201010 and 201001 instead of 20101. What you can do is create a new column where you use the REPT function. This function repeats a specific string for a specific number. For example REPT(“0″,2-LEN(MONTH(Tablix1[Date]))) would give me 0 for 2 – the length of my month function. If the length of my month is 2 it wouldn’t return a 0 if it is one it would return one 0.
My year month calculated column dax would look like:
=year(Tablix1[Date]) & REPT(“0″,2-LEN(MONTH(Tablix1[Date]))) & MONTH(Tablix1[Date])
I love music, i listen to it a lot on my ipod and on my computer.I also love statistics, I send all the tracks I listen to the online music service last.fm where I have stored all the tracks i have played since 2006.
This is a list of my top artists (yes I am kind of a metal head):
Wouldn’t it be great if I could load all my played tracks into the PivotViewer? This blog posts describes how I got all my played track information into the PivotViewer. In a previous blog post is described how you we can make a PivotViewer application on top of PowerPivot. Today we are going to load data from Last.FM into PowerPivot and base a PivotViewer app on top of PowerPivot.
Read more…
In my previous blog post I checked what the key influencer is for having a lot of stock using PowerPivot and Predixion Insight for Excel. Now i want to see what the number of units on stock will do in the future. I want to see this by country for the next 6 months.
We start in Excel, i have again loaded the same tables as before into PowerPivot containing the Factinventory and a table containing country’s and stores. This time we are going to use PowerPivot indirect since the data in PowerPivot is not really suited for the Forecast, besides we want to use an aggregated value for this and not all individual 8 million records we used before.
Read more…
