Quick Tips & Tricks « PowerPivotPro

The Friendly Neighborhood Operators && and ||

April 22, 2011

David Hager’s guest post below reminded me of something I’ve been meaning to share for awhile. Here’s a feature of PowerPivot so tiny, so sensible, that I’ve had a very hard time internalizing that Excel lacks it. In fact, I think I’ve had to re-check Excel 3-4 times to confirm for myself that Excel doesn’t have it.

The feature is the pair of logical operators && and ||. They are alternatives to the Excel functions AND() and OR(), respectively. Check out these calculated column formulas:

IF(Product[Color]=”Blue” && Product[Weight]>6, “Heavy and Blue”, “Everything Else”)

IF(Product[Color]=”Blue” || Product[Color]=”Red” || Product[Color]=”Yellow”, “Primary Color”, “Blend”)

The first example is looking for Products that are both blue AND weigh over 6 pounds. The second example will tag a product as “Primary Color” if it is Blue, Red, OR Yellow.

Notice how you can also use more than two clauses – neat huh?

Note that using && and || is often a great alternative to the dreaded “nested IF” formula.

Also, I mentioned above that Excel DOES have the functions AND() and OR() that do the same things. But these operators make for much easier formulas to read, and I think they are a lot easier to write as well.

Using || in a CALCULATE measure

I don’t think I’ve had occasion to use && in a CALCULATE measure, primarily because adding another filter clause to calculate achieves the same effect as an AND. But || sure is helpful, here’s an example:

[Return Dollars] = CALCULATE([Sales],
Sales[TransactionType]=”Return” ||
Sales[TransactionType]=”Credit”)

That measure recalculates the [Sales] measure and only includes transactions that were either Returns OR Credits. Neat huh? It’s a great shortcut alternative to adding a calculated column with a nested IF, or even a calc column that uses || – just do it right there in the measure.

And now for another alternative to nested IF’s: David’s post on using a lookup table.

7 Comments | DAX, Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

Guest Post: Calculating a Sum Based on a List Criteria

April 22, 2011

Guest Post By David Hager

It is a fairly easy task in PowerPivot to calculate a sum based on N criteria (after a learning curve). This can be accomplished by filtering your data before importing it in the PowerPivot window, selections made in the Pivot Table, or through some (relatively) simple DAX calculations.

However, if N gets too big, the task get much bigger. Now, imagine a table with thousands of customers and the task is to calculate a sum based on only 1000 of those customers. Conventional filtering does not provide a way to do this. Then, say that this list of customers changes from day to day. It would be nice to have a method to perform this calculation. There is!

The first step is to create a linked table from your Excel workbook to its PowerPivot window. The table in PowerPivot is named LookupList. Then, a relationship is created between that table and an existing Table1 (as shown below).

The list shown above does not have 1000 entries, and is just for demonstration purposes. However, I have tested it with >1500 entries and it works

Solution One: Calc Column as Basis for Measure

One solution is to create a calculated column named AggregateList that returns only the amounts that are associated with the customers in the lookup table. The correct formula to do this is:

=IF(ISBLANK(RELATED(LookupList[Customer])),BLANK(),Table1[AMOUNT])

Now, a pivot table can generate the desired result as shown below.

By changing the aggregation of the measure, an average or other value can also be obtained.

Solution Two: Yes/No Column plus CALCULATE Measure

(From Rob): Rather than produce the numerical column as David did above, my first instinct was to use the LookupTable to generate a simple yes/no column, and then use that as the filter in a CALCULATE measure.

Calc column:

=IF(RELATED(LookupList[Customer])<>"",1,0)

and then the measure:

=CALCULATE(SUM(Table1[AMOUNT]),Table1[IncludeCustomer]=1)

This is mostly a matter of style. Both require a calc column – this option’s calc column is simpler but has a more complex measure. (Neither approach is all that complicated however.) This option might make for a smaller file size, but it’s hard to be certain of that, and even if true, it won’t be much.

Back to David.

Refreshing the customer list

I mentioned at the beginning of this article that the customer list provided by the linked table is dynamic by nature. If the list is changed manually, recalculation will occur if the PowerPivot window is opened. However, if the list is generated from Excel formulas some calculation errors can occur. Instead, the use of other methods to update this list is preferable. The details of these issues will be discussed in a future article.

3 Comments | DAX, Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

Guest Post: Catalog Your Measures with a Nifty Macro

March 16, 2011

UPDATE: Uploaded a new version of the macro file that David provided that fixed a couple of small problems. Also, I fixed a typo (I had it as XSLM, not XLSM – thanks Dan!)

We have a guest post today from David Hager. Like most folks who exchange email with me, David is now aware of how… intermittent… an enterprise that is. Intermittent being the description of the replies one receives.

But he persevered. And this is a pretty ingenious macro he has written.

So ingenious, in fact, that he is bordering on revealing a few techniques that I have been debating whether or not to unmask. Things we do at Pivotstream that Microsoft considers unsupported. Which, of course, is the good stuff. David is traipsing around in some very dark corners.

The only clarification I will offer to his post, which appears below, is the following: When David says “this procedure finds where the measures are stored in the PowerPivot workbook,” I want to make clear that what is being found is actually where PowerPivot keeps a backup copy of the measures. The backup copy is always up to date, so it is very much reliable.

But the “real”definitions are actually stored elsewhere, in a place named Item1.data – and, like Forrest Gump, that’s all I have to say about that.

Take it away David…

Creating a Measures Table From PowerPivot Workbooks in a Folder

By David Hager

When defined name formulas are added to an Excel workbook, they can be easily accessed and viewed through the use of Excel’s Name Manager. However, when measures (formulas added to PowerPivot pivot tables and based on the DAX query language) are created, there is no way to know that they exist except for visually scanning the PowerPivot field list. An icon resembling a calculator will be to the right of each formula created in the field list. However, in order to view the DAX formula, the field must be right-clicked and “Edit Measure” selected. Then, if you wanted to store that measure somewhere for future reference, it can be copy/pasted to the desired location. This process becomes time-consuming if you have many PowerPivot workbooks that contain many measures. It would be nice if that process could be automated. To that end, the following VBA procedure for opening files in a folder and extracting the DAX measures and putting them in a table can be downloaded here.

(NOTE FROM ROB: You MUST rename that file to .XLSM before you can open it! WordPress does not allow upload of macro-enabled files, so I uploaded as XLSX. And if you don’t trust macros from other people, I respect that. Maybe we’ll upload a text only version with instructions for those of you in that camp.)

Most of the code deals with file and folder manipulation, and will not be discussed here. The core procedure is shown below.

Sub ExtractAndCopyMeasures()
Dim tText As String
Dim strStart As Long
Dim sCopy As String
Dim sCopyArray
Dim aCol As Range
Dim bCol As Range
Dim sItem

On Error Resume Next

tText = ActiveWorkbook.CustomXMLParts("http://gemini/workbookcustomization/MetadataRecovery _Information").XML

strStart = CLng(InStr(1, tText, "CREATE MEASURE"))
sCopy = Mid(tText, strStart, InStr(strStart, tText, "</") – strStart – 2)
sCopy = Replace(sCopy, " ", "")
sCopy = Replace(sCopy, Chr(10), "")
sCopy = Replace(sCopy, "<", "<")
sCopy = Replace(sCopy, ">", ">")
sCopyArray = Split(sCopy, ";")

For Each sItem In sCopyArray

Set aCol = ThisWorkbook.Worksheets("MeasureTable").Range("a1048576").End(xlUp).Offset(1, 0)
Set bCol = ThisWorkbook.Worksheets("MeasureTable").Range("b1048576").End(xlUp).Offset(1, 0)
aCol.Value = ActiveWorkbook.FullName
bCol.Value = Mid(sItem, InStr(1, sItem, ".") + 1)

End Sub

In essence, this procedure finds where the measures are stored in the PowerPivot workbook, parses the measures and place them in a table. Now, let’s examine some key parts of the code.

tText = ActiveWorkbook.CustomXMLParts("http://gemini/workbookcustomization/MetadataRecovery _Information").XML

This line loads into a variable the XML from the custom XML part in the workbook that contains the measures.

strStart = InStr(1, tText, "CREATE MEASURE")

This is the position in the XML string where the first measure is located.

sCopy = Mid(tText, strStart, InStr(strStart, tText, "</") – strStart – 2)

This code locates and stores just the part of the XML string that contains the measures.

sCopy = Replace(sCopy, " ", "")
sCopy = Replace(sCopy, Chr(10), "")
sCopy = Replace(sCopy, "<", "<")
sCopy = Replace(sCopy, ">", ">")

These lines of code clean up the string so that the formulas can be properly displayed. In particular, the terms "<" and ">" are used in XML for “<” and “>” respectively to prevent reading errors.

sCopyArray = Split(sCopy, ";")

The Split function converts the delimited string into a variant array containing each measure as an item in the array.

For Each sItem In sCopyArray

The final part of the procedure loops through the array. aCol is used to set the next empty row without using a counter. The measure is extracted from the array item by using Mid(sItem, InStr(1, sItem, ".") + 1) to start at the correct string postion.

I hope that you find this procedure useful!

Note: There may be some prompts that will have to be answered manually during the file opening process.

6 Comments | Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence VBA Macros | Permalink
Posted by powerpivotpro

More Fun With IF(VALUES())

March 12, 2011

“I mean, IF() I’m paying top dollar, I want a little production VALUE()”

I bet you thought I was out of movie quotes for IF(VALUES()) didn’t you! If I had a fake ID, my name would be “McPivot.”

Bag of Tricks

OK, once you have a reasonable level of comfort with the workings of IF(VALUES()), whether that comfort is “I understand it” or even just “I can make it give me the right results,” you are in for some treats. There are a number of ways to use IF(VALUES()) to produce different desired effects. I’m going to blast through a few of them here real quick.

Note: If you have not read the previous post, you should do so before reading this one.

Returning BLANK() for Subtotals and Grand Totals

Sometimes you end up with a measure that simply doesn’t make sense unless you are in the context of a single item. Returning to the football project, let’s say I write a measure that for “Personal Best Single Game Rushing Yards,” which, for a given football player, returns their personal best single-game total over all time:

Cool huh? (My data ends before Adrian Peterson broke Jamal Lewis’ record). By the way, the formula for that measure is:

=MAXX(ALL(DimProschedule[GameDate]),[Rushing Yards])

(To understand how that measure works, I refer you to one of my personal favorite posts, the Five-Point-Palm, Exploding Function Technique.)

Now, if you put another field on the pivot, you have a decision to make: do you want that measure to be evaluated at levels other than a single player? How about for an entire team? Or all players of a given position? You may decide that doesn’t make sense, and prefer that the measure NEVER return a value in those cases.

Good thing we have IF(VALUES()). We can create another measure based on the existing measure:

=IF(COUNTROWS(VALUES( CleanPlayers[PlayerID] ))=1,
[Best Single Gm Rush Yds], BLANK())

Notice how the original measure does not reflect the best player total? In the week that Lewis ran for 295, apparently the Ravens had other players run for a total of 39 additional yards. Depending on the semantics desired, you could argue that the Ravens’ number should be 295, for best single player performance in a game.

Or you can just return blank and forget about it, as my second measure does.

Returning different measures for different cases (aka “Branching”)

If we look at the picture above, you may say “hey it’s not fair to compare QB’s against RB’s in rushing yards, shouldn’t each position be measured by its primary metric – pass yards for QB, rush yards for RB, and receiving yards for WR’s and TE’s?”

Yeah, let’s do that. First let’s create similar “best game ever” measures for the other two kinds of yardage – passing (throwing) and receiving (catching). (Rushing is running, btw).

OK, cool, so we have all three measures, but it results in a scraggly pivot. I want a single column of numbers, and for the number to reflect the position (primary role) of the particular player.

IF(VALUES()) to the rescue. A new measure that “branches” into the right measure for each position:

   =IF(COUNTROWS(VALUES( CleanPlayers[PlayerID] ))=1,
      IF(VALUES(CleanPlayers[PositionCode])="QB", [Best Single Game Pass Yards],
      IF(VALUES(CleanPlayers[PositionCode])="RB", [Best Single Game Rush Yards],
      IF(VALUES(CleanPlayers[PositionCode])="WR", [Best Single Game Rec Yards],
      IF(VALUES(CleanPlayers[PositionCode])="TE", [Best Single Game Rec Yards],
      BLANK())
      ))),
      BLANK()
   )

Cool, huh? A few things to note:

IF(VALUES()) is used to check the value of [PositionCode], but [PositionCode] isn’t even on the pivot! This works because each PlayerID does correspond, in the CleanPlayers table, to a single position code, because each PlayerID is unique.
That said, it IS hard to tell which player is which position, especially for the 49ers, who had awful teams during the years for which I have data. So I’ll add Position to the pivot below.
I’m pretty sure that Johnnie Morton had his 153 yard receiving day when he played for the Lions, not the 49ers, but solving that problem is best saved for another day.

Here’s the pivot with position added in for clarity:

(I decided to screenshot the Bears section rather than 49ers because the Bears had better players in those years.)

1 Comment | DAX, Quick Tips & Tricks, The Great Football Project | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence DAX IF | Permalink
Posted by powerpivotpro

The Magic of IF(VALUES())

March 9, 2011

“I’m Dan Marino, and IF() anyone knows the VALUE() of protection, it’s me.”

Boom! An Ace Ventura quote finally graces the blog. Yes folks, that fine work of American cinema did indeed feature Dan Marino reprising his real-world ad for Isotoner.

(UPDATE: I had the val_if_true, val_if_false arguments reversed in my sample formulas, even though I had them correct in Excel. I’m rusty Smile . Fixed now.)

But more importantly, I wanted to revisit this little two-function combo: IF(VALUES()). I’ve covered it before a few times, but generally in the context of covering something else.

And since this is one of my favorite techniques, I think it deserves its own dedicated post. A series of posts, actually. Let’s dig in.

In Measures, You Can’t Just use IF()!

In regular Excel, the IF() function is a familiar tool to most of us:

IF(logical_test, value_if_true, value_if_false)

In PowerPivot, you can use precisely that same version of IF() in calculated columns. So, for example:

IF(Table1[Column1]>6, 0, 1)

In a measure formula, however, you cannot do that. If you do, you will receive the following error:

To better understand why that happens, let’s get ourselves an example…

“Whoa, did you say DimDown???”

Yeah that’s right, the Great Football Project is back after a long hiatus! I had a phone call yesterday with a Pittsburgh Steelers fan. You know Pittsburgh? It’s a drinking town with a football problem.

Anyway, he requested a return to the football project, under the umbrella of “how do the Steelers win another Super Bowl?” Given that I lived in Seattle for 13 years, and witnessed his Steelers literally “steel” a Super Bowl from Seattle via lopsided referee calls, I decided that we should look at what should have been the Seahawks’ championship season: 2005.

[Pct Successful Plays] is a measure that I’ve been developing with Hugh Millen. I’m not going to reveal the “secret sauce” behind said measure, but you can see that in Week 21, when the Seahawks played Pittsburgh in the title game, only 31.2% of their plays were successful – well under their 38.1% total for the year, and their 6th-worst percentage of the year.

Applying Different Formulas in Different Circumstances

Now let’s say that I want to return a different value for this measure in Week 21, which is Super Bowl week. You know… I want to “correct” for bad referee calls.

I’ll create a new measure, [Corrected Pct Successful Plays]. In normal cases, I want that measure to just return the same value as would be returned by the original [Pct Successful Plays] measure.

But in week 21, I want to double it. Just to set things straight. (In real business, there are many legitimate examples of this technique, like applying a different formula in the West region versus the East region of a sales territory).

If I just use IF() by itself, I will get the error I showed in the previous section.

But if I nest VALUES() inside of the IF(), it will work!

IF(VALUES(Schedule[WeekNbr])=21, [Pct Successful Plays]* 2,
[Pct Successful Plays] )

Neat, huh? Returns the same value as [Pct Successful Plays], except it doubles in Week 21.

A Return to the Golden Rules of DAX Measures: No Naked Columns!

So why is VALUES() required? Because you cannot have “naked” columns in your measures. This is one of the “golden rules” for DAX measures that I first introduced over a year ago – see this video for an explanation.

If you’re not interested in watching that video right now, fine, just take my word for it: in most places, you cannot just include column references in your measures, you have to wrap them in a valid function. (And go watch the video later, as it’s a concise explanation of the fundamentals of DAX measures, and something I cover on every consulting/training engagement).

VALUES(Table[Column]), however, IS legal in a measure, and VALUES returns the list of values for the specified column in the current context. So in the last row of the pivot above, it returns 21, and the formula becomes IF(21=21, 2 * [Pct Successful Plays], [Pct Successful Plays]).

Make sense? OK, good, because I am lying to you. There’s one more trick I have to show you, as the formula above actually STILL doesn’t work.

But… IF(VALUES()) only works when there is only a single value!

OK, here’s the formula from above, repeated here:

IF(VALUES(Schedule[WeekNbr])=21, [Pct Successful Plays] * 2,
[Pct Successful Plays])

On each individual row of the pivot above, [WeekNbr] DOES have a single value (1, 2, 3, … 21).

There are actually many cases, however, where [WeekNbr] does NOT have a single value.

For instance, in the Grand Total cell of the pivot:

In the grand total cell, [WeekNbr] is NOT a single value. It is actually ALL weeks. In that case, the VALUES function returns a full column of values, and comparing a whole column to a single value doesn’t work out too well.

I like to visualize that problem in my head:

IF(VALUES(Column)) yields an error when there is more than one value of Column

And by the way, that’s not an error that you will just see displayed in the pivot’s grand total cell. It will “tank” the evaluation of the entire pivot:

Protecting against multi-value situations

Protecting against this is pretty simple. You just use another IF() to “guard” your IF(VALUES()).

The “guard” if is highlighted below, wrapped around the original formula in normal font.

=IF(COUNTROWS(VALUES(Schedule[WeekNbr]))=1,
        IF(VALUES(Schedule[WeekNbr])=21, [Pct Successful Plays] * 2,
         [Pct Successful Plays]),
   [Pct Successful Plays])

In essence, you never let your IF(VALUES()) get evaluated unless there is only one value! If there is more than one value (as there is in the grand total, or in a subtotal), the formula above just returns the original measure (that’s the “[Pct Successful Plays]” at the end).

Making it simple (seriously, just follow the pattern!)

A lot to digest?

It may seem bad, yes. But you don’t actually have to grasp it fully. You can just treat this as a “pattern” that you use.

For example, here is the formula turned into a pattern. You just substitute your columns and values in the highlighted spots:

=IF(COUNTROWS(VALUES( <Your Column Here> ))=1,
        IF(VALUES( <Your Column Here> ) = <Test Value Here> ,
         <Result if True> ,
         <Result if False> ),
   <Result for subtotals and grandtotals> )

The stage is set for some serious fun!

OK, with all of that covered, that sets us up for a series of quick, simple, and powerful techniques that I can share in the next blog post.

5 Comments | DAX, Quick Tips & Tricks, The Great Football Project | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence DAX IF | Permalink
Posted by powerpivotpro

Date and Date/Time – Sneaky Data Types!

February 21, 2011

These Two Columns Both Contain Dates, Right? Sure They Do!

Relationships that should work, but aren’t?

Here’s a quick tip, one that I think we’ll all need sooner or later.

When I got back from last week’s amazing consulting/training trip, the team let me know that we had a problem. An existing PowerPivot model, one that had been working just fine for a long time, had stopped working when we refreshed the model with the latest data:

All the slicers were now indicating that there wasn’t any data. And those empty rectangles on the right? Those are charts. They just weren’t showing any data anymore.

What’s the problem?

We pretty quickly determined that it had something to do with the date relationships. When we cleared the date slicer, data came back. So it was just when we were filtering by Date that data disappeared.

In cases like that, the first thing I like to check is data types. Somehow, did one of the columns get changed from Date to Text? Nope, not this time. They both are definitely still Date, as evidenced by this screenshot from the PowerPivot window ribbon, here at right.

But Data Type Isn’t Enough!

Notice the Format option there, though? Try setting both related columns to a format that displays time as well as date:

Now look at the two columns:

Like I said, sneaky little Date/Time data type. When we got new data this time, one of the source systems in the loop decided to include the time of day, whereas before, it had been omitting it.

So yeah, the two columns don’t match up anymore even though by default they LOOKED like they matched. Sneaky.

So… if you have a relationship on two Date columns, and the relationship does not seem to be working (all of your measures are returning blanks), this is a likely culprit.

Two ways to fix this

The quickest fix is to create a “clean” calc column that strips the time:

=DATE(YEAR([CalendarDate]),MONTH([CalendarDate]),DAY([CalendarDate]))

Then you use the calc column for the relationships rather than the original.

But the better, more reliable way to clean your date columns is in the underlying SQL (as long as that’s an option). If your SQL sources always trim your Date columns down to pure Dates, and truncate Time, then you never have to write calc columns for this purpose again – solve it one place, and save yourself a lot of ongoing work.

Furthermore – columns imported from SQL end up being a lot better compressed by PowerPivot than calc columns, AND this often results in faster pivot performance as well.

If you do the trimming via Views, you can keep the time component in your SQL tables for later, in case you want to do “time of day” analyses. But your default views that you import should always be protected against this.

And remember, if you aren’t a SQL pro yourself, having a good relationship with the folks who run your databases is a very positive thing. At Pivotstream for example, I never touch SQL. But my colleagues who maintain SQL are very helpful, and our cooperation lets us do things that otherwise would be impossible. One of my favorite themes – cooperation between PowerPivot pros and SQL pros – and I will be hammering said theme every chance I get Smile

4 Comments | Data Import, Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Date Time Types | Permalink
Posted by powerpivotpro

Another way to get and use slicer values in formulas

December 9, 2010

Back in June, Kasper posted a trick which lets you detect a user’s selection in a slicer, and use that in a PowerPivot measure. That’s a very useful trick, one that we employ all the time at Pivotstream.

But sometimes, that is overkill. Sometimes, you just want to grab a slicer’s selected value and use it in an Excel formula, right there in the sheet. Here is the simplest method we have discovered so far:

1) Duplicate the field as a slicer AND a report filter

First step is to take the field you want to use as a slicer, and add it to your pivot both as a slicer, and as a report filter, as in this simple pivot:

Date Field Dragged to Both Slicer and Report Filter

2) Observe that the Report Filter “Tracks” the Slicer

OK, now click a date in that slicer. Look what happens to the report filter in the sheet:

Cool huh? Since they are the same field, the report filter has to always be in synch with the slicer. And unlike the performance penalty that can pile up with multiple slicers, duplicating a field like this will NOT make your pivot slower at all.

3) Use the Report Filter Cell in a Formula

Yeah, you probably see where this is going already: now you can reference the report filter cell in a formula, like this:

4) Clean up the visuals

Move the formula to a more centered location, change the font, and hide the row that contains the report filter:

Notes

I haven’t tried it but I am pretty sure this will work with regular pivots, too, not just PowerPivots.
If you select multiple items in the slicer, you will get the text “Multiple Items” in your formula instead of a single value. UPDATE: I have since written another post that covers this. It’s not a pretty technique but it gets the job done. Post is here: http://www.powerpivotpro.com/2011/09/catching-multiple-slicer-selections-in-a-formula/
When you really get started thinking about this, there’s really no limit to the cool tricks you can pull off. I’ll show a few more specific examples over time, but I’m sure you guys will discover many cool tricks of your own, too. Here’s some food for thought: report filters aren’t the only way to get slicer selections into a worksheet cell.

10 Comments | Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

PowerPivot for Excel 2007?

December 2, 2010

I see this question a lot. Excel 2010 sure looks a lot like Excel 2007 – they both use the Ribbon instead of traditional menus, and they both use the new XLSX file format. So I can understand why it seems like 2007 should work.

I’m sad to say that PowerPivot is NOT supported, and simply will not function, with Excel 2007.

Stuck on Excel 2007? Not all hope is lost…

First off, you can get a trial version of Excel 2010 by clicking here. If you are just in an evaluation phase right now, that likely will help you quite a bit.

The next thing to remember is that your entire organization does NOT need to upgrade to 2010. Only the PowerPivot authors need to have 2010, and then they can publish their work to SharePoint 2010 (and servers are often upgraded long before desktops). So this *might* help.

(Also keep in mind that your entire org does NOT have to adopt SharePoint, either. A single “departmental server” running on a reasonably capable desktop computer can do the job quite nicely).

Lastly, even your PowerPivot authors can still run 2007 for most tasks, and have 2010 installed side by side for PowerPivot tasks. That is sometimes a useful concession to IT.

OK, so why IS Excel 2010 required?

Is this some nefarious MS plot? Not this time. There are good reasons why 2007 is not supported, and I worked closely on two of them while I was at Microsoft.

Reason 1 – Slicers

The PowerPivot team (of which I was a member at the time) decided that PowerPivot was not going to “shine” in customers eyes with the existing appearance of Excel. A more engaging, “Fisher Price” style of interaction with a finished report was required.

(I can confirm the truth of this from my experience on the Great Football Project in 2006 – our focus group participants BADLY needed some sort of modern-day filtering/exploration method, and Excel 2007 simply didn’t have it).

So the PowerPivot team actually “donated” a bunch of people, for about two years, to the Excel team in order to build the Slicers feature into Excel 2010 – as illustrated by Region, Territory, and DR-ST below:

Reason 2 – Embedded Data

The second reason is the bigger one, the one that made it impossible to support Excel 2007.

It was very much a requirement that PowerPivot be able to store its data inside the XLSX workbook, rather than in a separate file. Imagine, as an Excel user, if you were told you had to lug two files around everywhere just to make your pivottables work.

So, Excel 2010 had to invent a mechanism for allowing other applications to store their data inside of workbooks, and for allowing that data to be *fetched* by those applications WHILE EXCEL IS RUNNING, which um, normally locks other applications out of the file.

This was some of the most imaginative engineering I have ever witnessed, on the part of some amazing engineers like Shahar Prish and Raman Iyer. (My role was essentially High Priest… and Drawer of Pretty Diagrams).

So, today, you can take a PowerPivot workbook, rename it from .XLSX to .ZIP, and then navigate into XLCustomData and see a file named Item1.data:

Anyway, hopefully that clears up the questions – “does PowerPivot work with Excel 2007,” “what do I do now that I know it doesn’t,” and “why DOESN’T it work?”

16 Comments | Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Excel 2007 | Permalink
Posted by powerpivotpro

Add a “Last Refreshed Date” Readout

November 1, 2010

“Because there is nothing worse than a stale report.”

OK, so you’ve built some killer models and reports. You’ve published them to SharePoint. You’ve scheduled automatic refresh to run, say, once per week.

You have this PowerPivot thing All. Dialed. In.

But are the report consumers satisfied? Hell no, they aren’t satisfied! If there’s one thing you can be certain of, your report consumers will NEVER be satisfied.

And hey, that’s kinda cool. It’s a good thing. You weren’t given all this new power just so you can sit on your laurels, now were you? Nope. True PowerPivot pros aren’t even sure they can FIND their laurels, much less sit on them. You will always be improving – both your skillset AND the reports you produce.

Today’s improvement: automatically informing the report consumers of how “fresh” the data is. It’s actually pretty simple.

Step One: Add a [LastRefreshed] Measure

Hopefully, somewhere in the PowerPivot window, you have a column whose most recent date is always the date on which the data was last refreshed.

For instance, in a retail system you might be able to use your Sales table for this, as long as there are no “holes” in your data in terms of dates (meaning you always have at least one transaction per day). Or perhaps the Calendar table you pull from the database is always current (and does not contain future dates). Or as a third option, perhaps you can get your db admin to add a single-cell table just for this purpose.

In this example, I’m going to use the TransactionDate column from my Sales table:

Did you know that measures can return dates? They sure can, and it’s killer useful. So let’s create a measure:

[LastRefreshed] = LASTDATE(Sales[TransactionDate])

LASTDATE() is kinda like MAX(), but for dates. So it will always return the most recent transaction date:

Neat huh? A date returned as a measure, in a pivot.

Step Two: Use that measure in a Cube Formula!

I think the most flexible, least intrusive way to display this measure in your report is to create a single cube formula for it, and then stuff that formula into a single cell. That way you have complete control over appearance.

Two things to note:

1) Given that your report will often have columns that are oddly sized to make everything look good, I’ve found it much better to use a single cell formula that includes the label, rather than splitting it across two cells.

2) Cube formulas, when they return a date measure, format it as an integer rather than a date. So you need to reformat it as a date IN THE FORMULA. (You could just use format cells, but since we are putting the label AND the date in a single cell, you can’t just format the cell as a date.)

Long story short: here is your cube formula:

="Last Refreshed: " & TEXT(CUBEVALUE("PowerPivotData",
"[Measures].[LastRefreshed]"),"mm/dd/yyyy")

Plop that in the desired cell, and you are all set:

Pretty slick, if I may say so myself.

That cell will refresh every time the workbook is refreshed. But it should not re-evaluate during an update, like when someone operates a slicer.

For more on refresh vs. update and how it can make a HUGE difference in performance, click here.

Leave a Comment » | Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Cube Formulas | Permalink
Posted by powerpivotpro

Illustrated guide to Excel Services Chart Rendering

October 4, 2010

COMMON QUESTION: “Hey, will this chart render unchanged in Excel Services, or will it look different?”

MY STOCK ANSWER: “Hmm, I forget, let’s try it out”

I hate giving that answer. So today, I ran a little test. Simple workbook, 12 charts that are all flavors of fancy. Not an exhaustive test, but a reasonable sample. Here are the 12 charts in Excel client (desktop). Click for a larger version.

12 Charts in Desktop Excel

Green Circle = Renders 100% identical in Excel Services
Red X = Renders less than 100% identical (sometimes small differences, sometimes large)

Now, here are the same 12 charts rendered in Excel Services:

Same 12 Charts, As Displayed on SharePoint (Excel Services)

Again, note that being flagged with an X isn’t the kiss of death – I just didn’t want to bog down in “grading” the differences. Sometimes the changes are big (top left chart) and sometimes the changes are astoundingly small (bottom middle chart).

Either way, having an illustrated reference is going to be helpful for us, so I thought I’d share it. Feel free to submit questions/comments/other examples. I may expand this reference over time.

4 Comments | PowerPivot as Online Service, Quick Tips & Tricks, SharePoint | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence Charts Visualization | Permalink
Posted by powerpivotpro

M	T	W	T	F	S	S
« Apr
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31