You’re “Poisson” Running Through My Veins: A Truly Epic Guest Post on Call Centers and Erlang C

July 8, 2014

Alice Cooper has Poisson Running Through His Veins, DAX-Style

“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:

  1. Brace yourselves for a dose of awesome.
  2. I don’t understand everything that’s happening in this guest post.
  3. So if you “get” all of this, fantastic. 
  4. If you don’t, don’t sweat it – just bask in the power of our toolet – it can truly do anything.
  5. 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.

Moving weighted average in PowerPivot

The wrong way to do this:

I include it here because the interactions between the eight weight measures are really, really neat to watch.

Read the rest of this entry »

Compare product performance after launch

June 26, 2014

By Avichal Singh

As part of the team we release products throughout the year –courseware, books, exams and eLearning (check out MVA for tons of free courses). We often need to understand how our products perform after launch and how they compare against each other (in the first month since launch, first quarter, first year etc.). For Example: we would compare the various courses we launched around Visual Studio 2010. We may compare Visual Studio 2010 against Visual Studio 2012 courses. We may even compare Visual Studio against SQL Server. Or compare adoption by geography or customer segments.

I can imagine similar need for other businesses, e.g. a car manufacturer who needs to compare performance of various year, make and models.

Power Pivot and Power View can allow us to go from View 1 below, which is inscrutable at best, to View 2 which really helps us understand and differentiate the adoption ramp of various products. In this article, I would explain how you can go from View 1 to View 2 using the car manufacturer example.

View 1: Monthly Sales by Car Model
Typical view available in BI, but not very insightful

Power View Graph Monthly Sales by Car Model


View 2: Cumulative Sales since Launch, by Car Model
Clear view into adoption ramp of various products

Power View Graph Cumulative Sales since launch by Car Model

Read the rest of this entry »

Experiments in Linear Regression, Part 2

June 19, 2014

<< Back to Part 1

Download this workbook Here

Slope of a Linear Regression Line - Now We Just Need to Translate That into Power Pivot

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

In Case of Emergency, Call JT Statmaster!

imageI 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.

Read the rest of this entry »

Experiments in Linear Regression Land, Part 1

June 18, 2014

Something About This Reminded Me of Fraser Crane Running With Scissors

Something About This Reminds Me of Fraser Crane Running With Scissors
(Click for YouTube)

Stand Back…

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?

Linear Trendline in Excel

A linear trendline in an Excel chart is an example of linear regression

Read the rest of this entry »

Week Ending Date Calculation

April 29, 2014

Guess Post by Scott Senkeresty at Tiny Lizard

imageJust a quick and practical tip today.

We have a really typical looking Date table.  However, we are going to be drawing some pretty charts summarized by weeks, and our business defines “end of week” at Saturday.  So, we need a new column in our Date table that stores this “Week Ending” date for each row.

The first thought to occur to me was “well, for each Year&WeekOfYear, I just want to grab the max date”.   That sounded easy enough… EARLIER() no longer scares me…
Read the rest of this entry »

Turning “OR” Slicers Into “AND” Slicers

April 3, 2014


In this Report, We Are Only Seeing Customers Who Have Purchased
Both Accessories AND Clothing During 2004

A Post From Oceanside!

imageYeah, I’m on vacation (my first real vacation in 5+ years), so why am I writing a post?  Well, it’s before 9 am, the family is still sleeping in, and I honestly loved the idea of slipping out to write a post while looking at the ocean. 

The truth is I LOVE writing these posts – in some sense they represent Peak Fun for me, especially when they can be written at a relaxed pace with no outside pressures.  In the future, maybe I will take vacations for the express purpose of writing.  (That sounds surprisingly good to me actually).

Slicers – The More You Select, the More You “Get”

Read the rest of this entry »

CALCULATE(): More Examples and Reinforcement

April 1, 2014

Guess Post by Scott Senkeresty


Rob is taking a much-needed vacation this week, so you get to hang out with me again.  Hurray for you!

When we last Became One With Calculate, I said in comments that I would “work on a visualization/graphic”.  I admit to spending far too much time trying to dream up the perfect visual, and kind of failing.  I am sure the elusive visual exists, but for now, I would like to reinforce our understanding of CALCULATE() with a few more examples.

We will again be partying with the Adventure Works, against this simple measure:
[Total Sales] := SUM(Sales[ExtendedAmount])

Example 1: Column Filter

[TotalSalesEurope] := CALCULATE([Total Sales], Territories[Continent] = “Europe”)

imageThis boolean parameter (aka: true/false parameter, column filter) says “Hey, Mr Dax Engine, I really don’t care what filter you had on Continent… cuz now it is Europe”.   Of course, we did nothing that would impact a filter on Product[Category], so each of the categories still have their own total sales.

I must admit… when I last wrote about CALCULATE(), I was thinking there was something fundamentally different and special about these true/false filters, compared to the table-style filters such as we see with FILTER() or ALL().

And indeed, they are kinda sorta almost special… in that they have a cute syntax and they have the potential to be much more efficient (in terms of speed).

However, functionally, the above measure is identical to the following measure:

=CALCULATE([Total Sales],
FILTER(ALL(Territories[Continent]),Territories[Continent] = “Europe”))

Read the rest of this entry »

Becoming one with CALCULATE()

March 20, 2014

Guest Post by Scott Senkeresty

Intro from Rob

Hey, it starts out simple and powerful:  CALCULATE is the SUMIF you always wished you’d had.  It works in pivots.  It’s the “anything IF.”  It’s amazing, really, how many doors it opens.

Of course, CALCULATE is designed to be powerful in ways we can’t even IMAGINE in our first day/week/month of using it.  You can spend years discovering all the things it can do – and that’s a good thing!  But sooner or later you’re going to hit something with CALCULATE that makes you scratch your head – why is it returning THOSE results?

I myself entered this twilight zone with the Precedence Project – a series of posts that I quickly abandoned.  It turns out that, practically speaking, you don’t need to achieve deep theoretical understanding of this stuff in order to achieve great results.

Below, however, Scott does a great job of resolving those mysteries.  And he does so by “channeling” two old friends who live at the base of the Alps.  Take it away, Scott…

Going to “Graduate School”


All right, so I’ve read Rob’s book a few times.  (Heck, I am credited as tech editor on it.)  I’ve devoured PowerPivotPro University.  So now what, I ask Rob?

“Go forth and conquer – data is your ocean,” is his answer.  He’s a practical sort of guy.  Me, though?  I’m never satisfied until I’ve completely torn the machine apart.

So, as I hinted in my last post, I went to graduate school and spent a few intense days engrossed in Marco and Alberto’s book.


Read the rest of this entry »

Speed: Another Reason to “Trim” Calendar Tables

February 25, 2014


An 11,000-Row Calendar Table Spanning from 2000 to 2030:
Most of the Time This is Harmless Overkill

A 60x Speed Improvement From a Most Ordinary Place

I’ve been doing some work lately for a client who really pushes the DAX envelope.  One of the top-three models I’ve ever worked on in terms of complexity, no doubt.  And really, my role is just to help fine-tune it and add a few bells and whistles.  They built this sucker themselves and I am way impressed.

Crazy stuff.  Formulas that use outlier dates from one Data table (“fact” table) to then subsequently filter another Data table (via its related Calendar table), but then wrap that up inside a MAXX inside a SUMX…  and it all makes perfect business sense.  It’s magic.

But speed ain’t its strong suit.  We tried all the usual tricks – “de-importing” unneeded columns, replacing calculated columns with imported versions, etc.

And it was still way too slow.  Then we tried something even simpler, and things got 60x faster.

Read the rest of this entry »

Optimal Set Selection, Power Pivot Plays Pokemon, and My Brain Just Exploded

February 13, 2014


Un.  Freaking.  Believable.
(See bottom of post for video of this in action!)

Back to Our “Regular” Programming Today

First of all, THANKS for being patient with the promos for PowerPivotPro University – we do have to pay the bills here of course, AND I have poured six months of my best work into it, so thanks for giving it your consideration.

But yeah, it’s time to get back to some serious magic tricks with data.

A Funny Thing Happened On Reddit and Twitter…

I don’t play Pokemon – neither the video nor card game variety.  But I recognize my kind of sickness (cough cough fantasy football) when I see it.

And people who know me, ALSO know my sickness.  So they point things out to me:

And that led me to this Reddit thread:  Pokemon & Power Pivot…

Read the rest of this entry »

“CONTAINSX” Revisited: What WAS the Match?

January 28, 2014

Building on a Popular Technique

Power Pivot Substring Match/Contains Grouping Column

Last week’s post on “CONTAINSX” proved to be quite popular.  In the comments, Sasha provided an alternate formula that used FILTER instead of SUMX.  Honestly I have a been of a “fetish” for SUMX – after all it IS the 5-Point Palm Exploding Function Technique – so at first I was like “nice work Sasha but I’m sticking with my SUMX.”

But then “en” asked if we could write a formula that reported what the matching keyword actually WAS – not just whether there WAS a match.

And then, Sasha’s formula came in super-handy.  A couple of quick mods and we were in business.  Read on for the formula, but first, a quick aside.

Another thing that is easier in Power Pivot than “Traditional” Excel

I really enjoyed the comments (and the emails) we received about the CONTAINSX post.  Here are two of my faves:

Read the rest of this entry »

Defanging the “contiguous date selections” error

January 16, 2014

Calculation Aborted.  Function DATEAD only works with contiguous date selections.  Power Pivot, why you gotta be so cryptic?

Have You Ever Seen This Cryptic Error?  Here’s a Quick Explanation and Fix Smile

So you’ve got a DATEADD measure…

Let’s say you’ve written a nifty measure called [Last Year Sales] that returns the sales value you had one year ago at the same time:

  [Last Year Sales] =

  CALCULATE([Total Sales, DATEADD(Calendar[Date], –1, Year))

And hey, that works great:


So far so good:  [Last Year Sales] Measure is Working Great!

Read the rest of this entry »