Using SUMX on an IF statement–Call Center Example

September 18, 2014

Guest Post by Elin Ramsey

PowerPivotPro Intro: I love real world examples. They really show how the tool is being used and the challenges you may encounter in a real world situation (like not having access to all the data you need, read how Elin tackles that).

Note how Elin slowly builds this example and keeps layering business logic in the DAX formulas. This comes naturally to the Business User who is intimately familiar with the workings and what is actually needed from the data. Throw in a third party and suddenly the process becomes much more burdensome as two parties need to communicate back and forth. In fact majority of the cost of any project (including BI projects) is Communication, read more here.

Building BI Faucets Then PlumbingI will go out on a limb and say “Business Intelligence belongs to the Business User”. The BI team is necessary in building the plumbing, but Business User should firmly be in charge of building the faucets. And build faucets before the plumbing. That is one of the stops in Rob’s amazing story (read or watch) that he recounted at PASS BA Conference. Here’s to building faucets! Elin, take it away…


Intro

My name is Elin Ramsey and I work as the Analytics Manager in a call center. Click here to see more about me. I’ll be reviewing a real world application of SUMX on an IF statement, as recently described here.

One thing I need to be able to estimate is the number of active customer support representatives we have on any given day. I can’t use HR or timecard data as I don’t have access to it. Another challenge is that some representatives will sometimes be acting as trainers most of a day and we don’t want to count them then.

I’m going to walk through what my definition of active is, how we set up the data, calculating whether a representative is active on any given day, using SUMX to calculate the number of active reps on any given day, and then a couple of examples of what we can do after we have that formula.

Read the rest of this entry »


Tuesday Grab Bag of Biz and Fun

September 16, 2014

Post by Rob Collie
 
mullet with labels

Today’s Post is Like a Mullet Haircut:  Business in the Front, Party in the Back

Let’s Go!

Series of quick updates today, mixture of pure fun and serious biz, sometimes simultaneously.  In this post:

  1. Download the Draft Index for the Online University – whether you are currently enrolled or not, this may be relevant.  Grab it here.
  2. Live Class in Philly – seats still open for Oct 21-22, more details here.
  3. “Secret Summit” Between Microsoft and Power Pivot early adopters last week.
  4. DAX:  The Power of Paper
  5. Yours Truly taken into police custody for Possession of Industrial-Grade Data Tools

 

Read the rest of this entry »


CALCULATE ( ) Lessons from Looney Tunes

September 4, 2014

By Matt Allington

In my last post I talked about a mistake I made early on in my DAX learning journey.  In that post I showed a formula that used CALCULATE ( ) to turn a row context into a filter context (AKA context transition).  Here is a quick refresh of the relevant part of that post.

calculate post

Since this post, I have been thinking about CALCULATE ( ) and wondering how to explain “WHY” CALCULATE ( ) creates context transition – this is the topic of today’s post.

I am sure there is a wide age profile of readers of this blog, and at least some of you would remember the Merrie Melodies cartoon “Cheese Chasers” where the dog (Marc) is sitting and using an ACME adding machine to make sense of what he is observing. [I know I said Looney Tunes in the title, but there is not a lot of difference and I thought more people would know what Loony Tunes was]. Before you watch this brief 40 second clip from the cartoon, let me first set the scene.  Everything is backward; the mice don’t eat cheese, the cat WANTS to be chased by the dog – you get the idea.   Spoiler alert – I will refer to the punch line below, so don’t read on until you watch the video if you want the full 1951 immersive experience.

Read the rest of this entry »


SUMX of IF: A Perfect Blend of Simple & Sophisticated

August 26, 2014

 
SUMX of IF Used to Make Grand Totals Add Up in Power Pivot DAX

In This Case, Getting the Grand Total Correct for Each Row Required SUMX

It’s that time of year again…

…when my love of spreadsheets actually translates into a love of sports.  Yes, it’s Compulsive Data Crunching Disease season, AKA Fantasy Football Season.

Fantasy football is a game in which the contestants assemble “portfolios” of NFL players in the same manner that you might build a portfolio of stocks and bonds.  Then your portfolio (we call it a “team”) performs well if the real-life NFL players perform well, and poorly if not.  The one difference between this and the stock market is that no two “portfolios” can contain the same NFL player – so if I get Peyton Manning, the other contestants in my league (typically 8-12 people) cannot have him.

I’m participating in a new form of league this year, one in which the contestants get to keep some of the players from prior years.  (In most fantasy football leagues, you start each year from a clean slate).

We’re going to be picking our players this weekend at an “auction” or “draft,” and naturally, I want to scout my opponents ahead of time.  Muhaha.

So, what do my opponents need?

A valid portfolio consists of:

Read the rest of this entry »


The Many Faces of VALUES()

August 21, 2014

Guest Post by Scott at Tiny LizardMany Values

Maybe it is a sign of where I am on the Geek Scale compared to Rob, but where he considers EARLIER() to be a pretty hard function to understand, it just doesn’t bother me.  At least it seems to have just one purpose in life.

Now, the VALUES() function on the other hand… well, that’s just some messed up stuff right there!  Not only does nothing about it feel natural and intuitive to me, but it also seems to behave in completely different ways depending on how and where it is used.

Basically, every time I use it, I feel like I either got lucky, pulled a fast one, and that I’m a dirty cheater.  So, at least I got that going for me.

Let’s look at some of the various usages.

Read the rest of this entry »


Filters CAN Flow Up Hill – Via Formulas That Is

August 7, 2014

By Matt Allington

Intro from Rob

imageMany of you already know of Matt Allington and his background as BI Director for Coca-Cola Asia Pacific.  Matt recently flew around the world (literally) to attend my training course with the purpose of becoming accredited by me as the official PowerPivotPro University Trainer for Australia (we even have a picture to prove it – displayed at right).

Now that Matt has my approval to teach my material, I have invited Matt to be a regular blogger on PowerPivotPro.com.  This is Matt’s first post in this capacity, so over to you Matt.

Confessions of a DAX student

Matt here:  I want to share with you a simple mistake I made early on in my DAX journey, and also create awareness of how easy it is to fall into a similar trap. This post will explain the mistake and provide the solution to how you can get filters to flow up hill – via formulae that is.

If you learnt DAX the Rob Collie way (like I did) you would be very familiar with Rob’s best practice of placing the lookup tables above the data tables in the PowerPivot Diagram View.

schema

The reason Rob teaches it this way is because it is very easy for the reader to visualise the flow of a filtered table – filters flow down hill.

Read the rest of this entry »


Toggling Between Different Units via Slicer?

July 29, 2014

Is this possible?

Someone at Microsoft asked me this question the other day:

“Sort of like how you’ve used a slicer for conditional formatting, is it possible to use a slicer to change the custom formatting of a number?  In my use case, I want to be able to display currency as either full number ($1,500,000.00) or abbreviated ($1.5M) as the viewer wishes.  See below for an example of the desire.”

Use a Slicer to Change Number Formatting from Raw to Millions/Thousands M/K?

Can We Do This in Power Pivot?

My Answer:  No, not possible.  Wait, maybe.  Hmm.  OK, yes, mostly.

All of these thoughts flashed before my eyes:

  1. Power Pivot measures/calc fields must always have a consistent data type.  You can’t have a measure return numbers sometimes and text other times, for instance.  All “exits” from an IF or a SWITCH must have the same data type.
  2. Apparently, #1 is no longer true in SSAS Tabular, in the 2014 release.  They now support “variant” data type measures. 
  3. But no, Power Pivot still lacks that “variant” measure capability, at least for now.
  4. Whoa, hold on a second.  The desired result above does NOT use different data types!  It’s all numbers!  So we just need to change the math!
  5. Oh, ouch, not so fast.  The “M” and the “K” – I don’t know how to add those labels in a numerical data type.

So this means…  text measures!

Read the rest of this entry »


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 microsoft.com/learning 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 StatisticsHowTo.com)

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 »