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…
“I Want to Taste You But Your Lips are Venomous, PWAH-SAAHHHNNN!!!!”
(Get It? Poisson/Poison? OK, Read on for a Bell Biv Devoe Reference)
Intro from Rob
Um, wow. A few things:
- Brace yourselves for a dose of awesome.
- I don’t understand everything that’s happening in this guest post.
- So if you “get” all of this, fantastic.
- If you don’t, don’t sweat it – just bask in the power of our toolet – it can truly do anything.
- Our new friend Josh is absolutely killing it with his song references.
Take it Away, Josh…
Since taking on a role in Work Force Management about a year ago, I’ve learned one thing: Staffing a call center is expensive. What I mean is: the staffing software, it’s is rather pricey. So much so, that smaller call centers just can’t afford the tools needed to easily create an accurate staffing model.
But as someone raised to the mantra of: “if you are going to do something, do it right” I decided to learn me some DAX. (To be fair though, what my dad really said was: “Aim low, that way no one can tell when you fail.” But for the sake of this post, we’ll go with the first quote. )
Luckily, Rob was nice enough to teach us the core of using complex equations in his Experiments in Linear Regressing, Parts 1 & 2. So we won’t be entirely lost in new territory, it’ll be more of a: “lost with friends and colleagues, ‘Danger Will Robinson’” sort of excursion.
Using RankX and SumX to create a weighted moving average
The staffing model I use relies on a weighted average of the 4 most recent weeks of incoming calls. Often times however, a week’s data may have been inaccurate, causing us to go a week further back.
The way a weighted average works is that each number is multiplied by the given weight and then divided by the the sum of all weights. So the weights 40, 30, 20, and 10 are assigned to the weeks, giving us an average number of calls that is more heavily influenced by the most recent week.
I include it here because the interactions between the eight weight measures are really, really neat to watch.
The Formula for the Slope of a Linear Regression Line. It’s Greek to Me.
(Get it? Greek? Sigh. Anyway, click the image to view the article on StatisticsHowTo.com)
In Case of Emergency, Call JT Statmaster!
I struggle mightily to understand formulas expressed as Greek symbols. I don’t know why really. Probably because it seems so abstract – that notation sacrifices “humanity” in order to achieve precision and uniformity. I get that, but it doesn’t make it easy for me.
Fortunately, we have people like JT Joyner. JT was a student in one of my classes late last year. And I dare say he was one of those “star pupils.” He took to Power Pivot like a natural. So natural, in fact, that a couple days after the class, he emailed me a Linear Regression workbook example, implemented in Power Pivot measures. He translated Greek into, you know, formulas.
Six months later, yeah, I am just getting around to doing something with it. But this is exciting stuff for sure. I’m pumped. Let’s dig in.
Something About This Reminds Me of Fraser Crane Running With Scissors
(Click for YouTube)
Remember, I am NOT a statistician. I slept through Statistics in college – and I mean in my dorm room bed, not even in the lecture hall.
I tend to learn via doing, and via teaching, so today’s post is a “forcing function” by which I attempt to stretch my brain and skill-set.
There’s an excellent chance, therefore, that I will do something wrong here. In fact I’m hoping many of you more stats-minded people will chime in here and correct/extend this where appropriate.
What the Heck IS a Linear Regression, Anyway?
A linear trendline in an Excel chart is an example of linear regression
Power Pivot is the Engine that Turns Data Into Information!
But We Can’t Understand This Properly Without Examining the Three Big Lies of Data
Goal: Answer Four Frequently-Asked Questions
So many things to say this week. Let’s jump in. Here are the questions I ultimately aim to answer, which are questions I get basically everywhere I go:
- How do all of the Power BI Components relate to each other? Power Pivot, Power Query, Power View, Power Map, Q and A, etc. = Power Confusion for some folks. I get it.
- Has Power Pivot become less important, now that we have all of these other new “Power *” tools?
- Which tool should I learn first in the Power BI family?
- Should I consider abandoning this stuff altogether in favor of <hot new technology X>? Tableau, Hadoop, R, etc.
In order to answer these, first we must confront some insidious lies that we are told every day.
Examining: The Three Big Lies of Data
Even though the Vendors are the Purveyors of these lies, they are NOT “at fault” for them. Because the world actually WANTS to be told these lies. BADLY wants to be told them, in fact. And because the audience is so receptive to these lies, the vendors naturally learn to tell them, and tell them well.
Vendors who DON’T learn to tell these lies? Well, those vendors don’t win many customers. And then those vendors disappear.
So while the lies COME from the vendors, the PROBLEM, really, is with US – the people who BUY the tools.
It’s Possible that Maybe I Added Something to Their Banner Image 🙂
(Click Image to Visit Site)
Year One Was Great, So I’m Speaking Again – San Jose CA, May 7-9
I really enjoyed the inaugural PASS Business Analytics Conference, aka PASS BACON, last year. Before that, I’d never been to a conference that was even remotely aimed at “my kind of people.” That all changed for me last year. I think we have found our home.
THIS is the kind of conference we need – one that’s accessible without heavy technology expertise. One where Business Impact take center stage, and the only pre-requisite is that you enjoy working with data. (And it helps if you don’t flinch at the word “Microsoft.”)
Which suits us Excel folks to a tee, doesn’t it? They might as well put us on the front page. But an explicit mention of Excel would scare off those other people – the non-Excel people (that we outnumber 100 to 1). Honestly I think we should just take this thing over. You know, like… Occupy BACON. (Brief pause while I replace the previously-bland title of this post with precisely that).
Putting my time where my mouth is, this year I am “doubling down” and presenting two sessions – a full-day “pre-con” and a “regular” session – see below for details. AND I’m in discussions about presenting a third session as well, stay tuned on that.
A little bird told us that last year, more people signed up for PASS BACON using the PowerPivotPro discount code than any other discount code.
Pretty cool, eh? Anyway, there’s a discount code again this year, but I’ve been slow to share it. Let’s pretend we did that to give all those “non-Excel” websites a head start, given that whole “100 to 1 outnumber” thing (in reality I have just been swamped).
“Only a master of evil formulas, Darth!”
”…hmm, which kinda DOES make you the master doesn’t it? Crap, I give up, just
chop me in half and get it over with, but make sure Luke sees you do it ok?”
Anakin’s Second Line of Questioning
Two weeks ago I posted about a particular student’s knack for making me learn new stuff, specifically the “bubble up ranks” method. (Ooh, I forgot to circle back and cover the “bubble up exceptions” part – making a note of that now).
Anyway, he also pressed me to teach STDDEVX(). That’s a first. “No worries,” I said, “it’s just another X function – but only you will know if the numbers are correct, because I don’t know what Standard Deviation truly means.”
Embarrassing isn’t it? To be a numbers guy and not know what Standard Deviation means? I was on the varsity calculus team in high school. I even have a Math major (to go along with my Computer Science and Philosophy majors – jack of all trades, master of none). How did I slip through the cracks on Standard Deviation?
OK yeah, it measures deviation, variability. I get that from the name. And yeah I even “learned” it in Statistics class in college, to the extent I ever showed up at 8 am. But I didn’t grok it. Didn’t breathe it in deeply. Until now.