Out of 407 Total Combinations of Subcategory and Region, These 8 Stand Out This Month (These 8 Combinations Differed GREATLY from their Respective 12-month Averages) A Post Long in the Thinking: Formulas that “Scan” (What’s this, you say? An actual…
Post by Rob Collie
Does Your Pivot Look Like This? Does its Slow make you Sad? Time for a Fix!
Tell me if this sounds familiar…
Yes, you know that pivots are meant to show aggregations. Summaries. Pivots were NOT invented to display thousands of rows of detail data.
But still, sometimes you need to do precisely that. The biz needs its list of customers and how much they’ve been buying, for instance, and all that data is in YOUR Power Pivot model.
And hey, pivots are really the only game in town* for table-shaped display of data. So, you build one of the monstrosities like the above.
(*OK yeah, you DO know about this thing called DAX Query Tables, but those are seriously a pain to set up. So, no. You rule those out before even starting. Just like me!
So You Do The Flattened Pivot Dance, Right???
In pictorial form…
The Flattened Pivot Option – Found in the PivotTable Dropdown in the Power Pivot Window
Next, You Pile a Whole Bunch of Fields Onto Rows
Then, on the PivotTable Design Ribbon Tab, You Turn Off All Subtotals
And Voila! It’s Slow as Heck.
by Matt Allington I have learnt a lot over the last year working full time as a Power Pivot professional. And some of the things that I dismissed as “not useful” very early on have turned out to be very…
Post by Rob Collie
Did Someone Say Deliberately “Misuse” a DAX Function for Our Benefit? We’re IN!
An End to Nested IF’s? Sign Us Up!
When we first saw the SWITCH function make its debut in Power Pivot a few years back, it was a “hallelujah” moment.
Whereas we used to have to write nested IF’s, such as this:
Now , with SWITCH, we could write that much more cleanly as:
Which do you prefer? It’s easy to make a strong “case for SWITCH,” isn’t it?
But What About Cases Other than Equals?
Now, let’s consider the following nested IF:
Notice that we’ve swapped out “=” for “<”.
And we can’t do that as a SWITCH, because SWITCH checks for exact matches between [Measure1] and 1 (or 2, 3, etc.)
This is unfortunate, because in these cases, we’ve had to keep using nested IF’s. And wow do I (Rob) *hate* nested IF’s. I can never seem to match the parentheses up correctly on the first try.
But There’s a Sneaky Antidote! We CAN Still Use SWITCH!
Post by Rob Collie
In 2004, There Were 2,561 Customers Who Bought Something in the Southwest.
But How Many of Those Were Repeat Customers? Depending on How We
Define “Repeat,” We Can Get at Least Three Different Answers.
A Right Turn at Albuquerque…
I sat down today to write about “Disconnected Cube Formulas” – yes, you heard that right. A brand new technique that I think has some pretty exciting (yet admittedly narrow) applications.
But along the way, like Bugs Bunny, I ended up doing something at least as interesting. So let’s do that one first.
Setting Up the Problem
I have four relevant tables: Territories, Customers, Calendar, and Sales:
The first three are Lookup (aka Dimension) tables, and Sales is a Data (aka Fact) table.
Active Customers is a pretty easy formula:
Post by Rob Collie
If We Use Excel’s Built-In Top N Filter to See Our Top 1,000 Customers, It Hides the Other Customers Completely. But Using DAX, We Can Just “Split” the Audience into Two Groups.
This Came Up Recently…
Hey, I absolutely ADORE the TOP N filter capability offered by all Excel Pivots. It kicks major booty and we use it all the time:
The Top 10 / Top N Value Filter in Pivots: Get to Know It, It Does Amazing Things
But If we set that to show us the top 10 customers, it shows us JUST those 10 customers:
OK cool, we see those top ten customers, and that they collectively purchased $132,026 of stuff from us.
But we want to ALSO see how much the OTHER (non top 10) customers are worth too.
Our “Morning” Website Traffic is Down 21.5% in Jan 2014 vs. Jan 2013, But “Evening”
Traffic is Up by a Similar Amount, and Full-Day Traffic is “Flat” at +0.9%
(Fake Data, But Real Analysis)
Two Different Flavors of “Time”
Usually, when we talk about “time” in Power Pivot, we’re talking about the Calendar/Date flavor: How much have things changed from yesterday to today. What are our Month to Date numbers, and how do those compare against the same period last year? Let’s call this “macro-trending.”
But time of day is also often interesting: what are the trends WITHIN a day? Let’s call this “micro-trending.”
And then, the hybrid of the two: how are our “micro” trends changing over the course of the year, month, quarter, etc?
I don’t think the techniques here are terribly complicated, but they might be a little difficult to conjure up on your own. So, it’s time for a post – and a downloadable workbook!
The Key: Separate the Date and Time Components!
Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from powerpivotpro.com, from searching the Web, as well as some of my own ideas. While some of the techniques I will talk about in this post are not new, I have combined some of these old favourites with some new techniques to solve some of the problems I have come across. I want to illustrate how the combination of these things can deliver a very positive user experience, and just as importantly – anyone with a good set of Excel skills can build an interactive charting tool like this by following the patterns demonstrated. I have created a demo of all of these concepts into a new workbook using Adventure Works so that you can see how these techniques come together for the user. There is a link to this workbook at the end of this post.
The techniques I have used are:
- Disconnected slicers used to create interactive chart series
- Cube formulae and standard Excel to make an interactive chart title
I love these 2 tips I learnt from Rob – so user friendly. However I came across a few problems when I tried to implement these, hence I have developed the following 3 additional techniques to solve these problems.
- Cube formulae and standard Excel to make an interactive legend
- VBA and “link to source” for interactive axis formatting
- Excel VBA to change which Axis the series appears on.
I have created a short video to demonstrate the 5 features built into this workbook, and I then explain each of these in more detail below. I have not hidden the behind the scene workings so you can see these in action – of course you would normally hide these from the user.
Now let me call out the key techniques I have used to make this workbook rock.