Would You Feed This Data Set Into PowerPivot? I Would.
“PowerPivot” is Really Just “BetterPivot” – PowerPivot Shines No Matter How Much Data You Have
I was having a conversation this weekend with a great guy who happens to be a business consultant and a hard-core convert to PowerPivot. A True Believer.
At one point he mentioned that only about half of his clients “have enough data to benefit from PowerPivot.”
That’s a myth, but I can’t blame him (or anyone else) for unconsciously believing it. People like me are responsible for the myth in the first place, because we love showing how PowerPivot takes complex data sets and chews them up. We like drama.
But guess what folks? About half of my PowerPivot usage is analysis on 50 rows or less. Let me show you why, using this made-up data set.
A Fake Survey With 50 Respondents
Let’s assume that my company makes and sells Bread Pudding. So we are trying to figure out where “Bread Pudding Likers” are most concentrated. That is our GOAL – that is the Insight we are trying to derive.
And we’ve run a survey of people in three different Luxury Dessert Clubs (the Bronze, Silver, and Gold programs) to see what desserts they like.
Step 1: Row Count Measure
[Responses] Measure: =COUNTROWS(Survey)
If I want to count rows in a normal pivot, I’d drag the Name field onto Values and do a count. Assuming I have no duplicate names, that would work fine.
So far though, since I don’t have dupe names, there’s no real benefit to using PowerPivot.
Step 2: How Many Like Bread Pudding?
Remember, my company makes Bread Pudding. So naturally I want to start by seeing how many of our respondents like my product.
In a normal pivot I’d start by just checking the checkbox for [Likes Bread Pudding] and getting a second column:
Normal Pivot Leaves Me Wondering About Percentages
But what percentage is that? 4 out of 22 Bronze program respondents like Bread Pudding. Do I do the arithmetic myself? No, with PowerPivot, let’s replace the “Sum of Likes Bread Pudding” with a real measure or two:
[BreadPuddingLikers] = SUM(Survey[Likes Bread Pudding])
[Pct BreadPuddingLikers] = [BreadPuddingLikers] / [Responses]
Now I can remove two fields from the pivot and just keep the last measure:
I Do NOT Need to Keep the Other Two Values on the Pivot:
I Can View My Pct Measure “Standalone” for a More Compact Report
Thoughts so far
So far we can say 3 things:
- Most people don’t like Bread Pudding – only 26% of the survey population.
- Gold program participants are most likely to like BP (Bread Pudding), at 38%
- I’m going to try digging deeper and seeing whether I can find an audience that’s higher than 38%
Step 3: What Percentage Like Cake AND Bread Pudding? Is Cake-Liking an Indicator of Liking Bread Pudding?
I’ve Repeated the Previous Measure Pattern for CakeLikers. But What’s the Overlap?
38% of Gold folks like Bread Pudding. 69% of them like Cake.
But what percentage likes BOTH? Let’s add LikesCake as a slicer:
OK, so 44% of all Gold CakeLikers also like Bread Pudding.
OK, so 44% of all Gold CakeLikers also like Bread Pudding. That’s useful info that we can’t get from a regular pivot! But what percentage of ALL Gold folks like both?
[Pct BPLikers in All Program] =
[BreadPuddingLikers] /
CALCULATE( [Responses],
ALLEXCEPT(Survey, Survey[Program])
)
So… of the entire Gold program, only 31% like both Cake AND BP,
Which is less than the Original 38%
Both of those pieces of information are valuable – less than half of all Gold CakeLikers also like BP, AND the 31% is of the overall Gold population is smaller than the 38% baseline I established for Gold up-front. So maybe Cake isn’t a great “sub-indicator”.
Step 4: Expanding the Slicer Set
Now I can play around a little.
Added Mousse and Pie Slicers
100% of Gold Folks Who Like Mousse AND Cake Also Like BP!
Oh Right, that’s only 8% of all Gold folks.
OK That’s Interesting. Maybe Pie is a Good Secondary Indicator, Behind Gold.
The Perils of Invented Data 
OK, it turns out there weren’t any dramatic conclusions in the data set I invented. Too late for me to tweak the data, this post has already taken a couple of hours 
So here’s a REAL conclusion I pulled from a REAL survey, again using PowerPivot, and again with a relatively small data set (one table, dozens of rows)…
Pretty Clear Evidence: The Longer You’ve Been Using PowerPivot,
The More Likely You Are to Upload Workbooks
See? I’m not kidding. PowerPivot is the way to analyze your data, regardless of size or complexity.
Caveat: A Nod to Data Mining
If I were going to use a tool other than PowerPivot to break down survey results, I would use a Data Mining tool like Predixion. Analyzing the characteristics of individual human beings is a PERFECT fit for a Data Mining tool, since it goes and finds the correlations for me, without having to add bunches of slicers and keep remembering the results from step to step.
Speaking of which, I may need to go dust off my Predixion account…
