Three Goals

In today’s article, I’m aiming to achieve three overlapping goals:

  1. Provide an intro to the concept of Value Above Replacement (aka VAR) – if you’re unfamiliar with this concept, I think you will find it stimulating.  I’m not sure how much application it has in the business world, but when I first encountered it, it fundamentally changed me – and in all honesty, that was when I first fell in love with data.
  2. Demonstrate a rudimentary VAR analysis in Power BI – just in case there IS a use for it in your biz life.  And also because hey, if I do this, it provides some legitimacy to goals 1 and 3.
  3. Make a data-driven case to my colleagues that we should change the rules of our fantasy football league – rather than just the occasional snarky message in Slack, it’s time for me to plead my case with data.  (Feel free to ignore this third goal, even if it is disproportionately motivating for yours truly).

Basic Concept:  Absolute Value Can Be Deceiving

Let’s say you’re trying to improve the fuel efficiency of your car, and you have a budget of $100 to make those improvements.  Furthermore, let’s say that you can only spend your budget on three different kinds of improvements:  better tires, better spark plugs, and a better exhaust system.

The simple answer, of course, is to buy the Best of each part – the best Tires, the best Sparkplugs, and the best Exhaust System.

But what if Reality rears its head and rudely insists that the Best Costs More?  What if there are a range of options available in each bucket, with varying price AND effectiveness?  Then what?

Introducing the "Value Above Replacement" Concept Before We Model it in Power BI

How Do You Spend Your $100 for Maximum Miles per Gallon (mpg) Boost?
(You Can’t Afford to Just Buy “Best” from Each Column)

The first instinct is to buy that $40 set of tires, right?  After all, they do offer the single-biggest mpg boost, at 2.2, and the best options in Sparkplugs and Exhaust don’t come close to that.

(NOTE that in this example, an ENTIRE SET of tires costs $40 TOTAL – unrealistic, but all of the prices here are unrealistic just to keep things simple.)

Once you play around with it a bit, though, you discover that you’re actually best-off with the second-tier Tires (at $30) combined with the top-tier Sparkplugs ($40) and the second-tier Exhaust.  Combined, those fit the budget of $100 and provide +5.4 mpg of fuel efficiency.  No other combo (that we can afford) matches or exceeds that.

Introducing the "Value Above Replacement" Concept Before We Model it in Power BI

Optimal Way to Spend Your Budget:  Get the Top-Tier Sparkplugs and Mid-Tier Tires/Exhaust

Stated differently, the top-end Sparkplugs are the most valuable product!  And the reason WHY…  actually has NOTHING to do with the Sparkplugs in question (the top-tier plugs)!

It’s all about the NEXT BEST option, and how “steep” is the dropoff – those second-tier Sparkplugs are a big drop in performance from the top-tier (a full 0.4 mpg), whereas the 2nd-tier options in the other categories are each only a 0.1 mpg dropoff from the top options.

Let’s Complicate Things a Bit

OK, now let’s increase the prices of all three tiers by $10 each, AND introduce a fourth, free tier…

Continuing the "Value Above Replacement" Concept Before We Model it in Power BI

With the Prices of the Original Three Tiers Each Increased by $10, and the Addition of a Fourth, Free Tier,
Our Optimal Solution is to Max Out Tires and Sparkplugs and goes with the Free Exhaust System

Neat, huh?  Now, our best bet is to be cheapskates on Exhaust and go “all in” on Tires as well as Sparkplugs.  This gives us a boost of 5.2 mpg – not as good as before, but hey, we have the same budget, AND the prices of all the “good” options have increased, so this shouldn’t be THAT big of a surprise.

But why not “skimp” on Tires instead, and pair a maxed-out Exhaust with our maxed-out Sparkplugs?  I mean, those free Tires provide +1.7 mpg and the free Exhaust only 1.4…

The answer, again, lies in the dropoff “slope” between options:

It's All About the Dropoff Curves

It’s Better to Skimp on Exhaust Because of its Lesser Dropoff Relative to Tires
(That’s a bit of an oversimplification because ALL of the dropoffs matter as well as the pricing tiers – but conceptually this provides a valuable intuitive feel)

Value Above Replacement:  Subtract the Value of the “Free” Options

So we have now seen that in multiple-choice, multi-category problems like the above, the absolute ROI of each option does NOT determine its ACTUAL value.  As illustrated above, the raw mpg increase of each option is not an accurate guide to our overall greatest value.  You can’t even rely on simple math like “divide the mpg boost by the price,” in fact!

The slope of the dropoff between options, and how those slopes compare across categories – THAT is the most important factor.  Which makes things pretty complicated.  But here’s a crude trick that gets us closer…

The Free Products are the "Replacement Level" Products

Let’s Take the Performance Boost of the “Free” Tier, and Make That Our New “Zero…”
…by subtracting the mpg of each free option from all the products of its category


This yields a new view of our options…  the Value Above Replacement!

The Value of Each Product is Now Recalculated as VAR

Each Option’s mpg Boost is Now Displayed as its Value Over Replacement
(Its mpg boost as compared to the free – or “replacement” – option in its own category)

This Value Above Replacement (VAR) view of things is quite a bit clearer than the “raw” pricecards we were using before.  That top-tier Sparkplug really stands out now (0.8 VAR), and it’s a little easier to see why we like the top-tier Tires (0.5 VAR) better than the top-tier Exhaust system (0.4 VAR).

Of course, as the pricing of the relative options becomes more nuanced – as we’d expect in Ye Olde Real World – VAR alone might not be a sufficient guide to our decisionmaking.  “VAR per dollar” might become a better guide, for instance, and of course to do a truly optimal job, we’d want to employ some sort of Solver/Goal Seek type of thing.

But as far as Goal #1 goes (introduce the uninitiated to the concept of VAR), I think this will suffice.  And also, it’s set me up to do some DAX!

Let’s use some Real World Data!

 “Enough with these cartoonish, contrived examples,” you say?  “Give us a meaty, real-world example full of noise!”  Well, friends, I have one such data set right here…

Behold, last year’s National Football League statistics for four different player types!

Four Power BI Tables of Fantasy Football Data from 2018

Smeagol contemplates the motivations for all of this.“Hey Rob, you said these were gonna be actual, real-world, NFL statistics from 2018.  What the heck is FanPts?”  Well, it’s a calculation based off of real-world NFL statistics, meant to capture a player’s overall performance… so that we can play an obsessive, data-driven game for sixteen weeks each year.

The formula for FanPts varies depending on the preferences of the group you’re playing with, and is pretty boring and straightforward, but here’s are the actual underlying statistics:

Fantasy Points Can be Calculated in DAX, but the Formula is Boring So Don't Worry About It

Fantasy Points are a Simple Calculation Based Off of the Real World Statistics

Let’s Set up a DAX Problem, Shall We?

Don’t like football?  No worries!  We’re just gonna treat “FanPts” as our Value score (like “mpg boost” with the first example) and proceed into some good old DAX.

Remember: to calculate VAR, we need to know the value we can get for free, treat that as zero, and subtract that from everything else.

But there’s no “Price” column anywhere in my data!

In Our Power BI Tables of Fantasy Football Data from 2018, There's No "Price"

FanPts is Our Value Column, But There Isn’t a Price Column.
So how do we know what defines “Free?”

“Free” = the first player we DON’T pick at each position

Instead of a numeric “price” for a player, fantasy football operates by a “take turns picking the player you want” approach.  (There are other more complex formats than this, but taking turns picking players is a tried and true approach).

So let’s pretend that you and I are going to play fantasy football with PERFECT knowledge of what is going to happen!  We already know each player’s 2018 performance, PRECISELY, and how many fantasy points (aka Value) they’re going to generate.

I’LL EVEN LET YOU GO FIRST!  Pick whomever you want!

The wrinkle is that at SOME positions, we get to pick more than one player…

We Consider the "First Unused" Player as the Replacement/Free Player

If there are two of us playing and we each get to select 1 QB, 2 RB’s, 3 WR’s, and 1 TE,
that makes QB #3 “free,” as well as RB #5, WR #7, and TE #3

After we’re done choosing our players, you can always change your mind and grab one of the UNSELECTED players later, and that sort of swap costs you basically nothing.  Think of the unselected players just sitting around in a “pool” of available replacements.  Free, in other words.

So, in English, the formula for finding the “free” player in each Position (aka category), is this:

Find the Nth-ranked player at each position,
where N = [Number of People Playing the Game] * [Number of Selections We Get to Make at that Position] + 1

That player’s value (FanPts) is our “Zero” for that position, aka the replacement value.

Let’s Do That in DAX!

First, I define a handful of parameter measures, so I can easily change the rules of the game and see the impact…

TeamsInLeague = 2
QBStarters = 1
RBStarters = 2
WRStarters = 3
TEStarters = 1

And then, a measure for [NVal] – to determine the value of N for Nth-ranked player:

Power BI DAX Measure for NVal:  An Input to Our Replacement Level Measure

That one is pretty straightforward – the only wrinkle is that IF(HASONEVALUE()) part, which is necessary to prevent an error in my measure.

Now for my [Repl Lvl] measure, which finds the “free” value for each position:

Power BI DAX Measure for Replacement Level

Whoa, two nested TOPN’s!  I do that because I want the Nth-ranked player’s value, which is essentially a MIDDLEN, but I’m not aware of a DAX function that does MIDDLEN all in one swoop (I’m an oldtimer now on DAX, so if you know of one, hit me up).  So I go back to my tried and true – if you want the Nth value, take the Bottom 1 of the Top N!

Yes, I am aware there are fancier ways to do this, using ADDCOLUMNS for instance, but I’m stubborn and I like my “Bottom 1 of Top N” – until they give us a MIDDLEN() function heh heh.

And since there’s no BOTTOMN() function in DAX either, I have to “transform” TOPN() to Bottom N by using the “ASC” sort order argument…

Power BI DAX Measure for Replacement Level:  Why Nested TOPN()'s?

All right, so why AVERAGEX()?  I do that because TOPN can always return more than 1 row, even when I explicitly provide 1 as the first argument.  If there are two players tied at that Nth rank, I will get both of them back. MINX or MAXX would also be fine here since they will have the same score in the event of a tie, but I better not do SUMX for instance Smile

OK, so that yields this report:

Power BI DAX Measure for Replacement Level.  But now we need one more measure.

Each Position Has its Own Unique [Repl Lvl] Value
(We don’t see TE’s at all here because I’ve sorted by “raw” FanPts, and TE’s score the least)

And then it’s a very simple measure to calculate VAR!

Value Above Replacement: The DAX for this is pretty simple.

(I named it V_A_R with underscores to hopefully eliminate confusion with the VAR keyword.)

Value Above Replacement: A DAX measure to win your fantasy football league. (As long as you have perfect clairvoyance on next year's stats).

Players Sorted by [V_A_R]: a Great “Cheatsheet” for Playing Fantasy Football Against Me
(If We’re Using 2018’s Statistics in the Rear View Mirror, That Is)

A little boring perhaps that Patrick Mahomes is still #1.  Yes, he was ranked #1 in raw [FanPts] and is STILL ranked #1 by [V_A_R].  And then number 2 and number 3 also followed suit.

But CHECK OUT Travis Kelce!  The #1-performing TE was ranked 25th by raw [FanPts], but he jumps all the way up to 4th when we rank by Value Above Replacement!

“But What if There are TEN People Playing and Not Just Two of Us?”

I am SO GLAD you asked.  Let’s bump our [TeamsInLeague] parameter/measure up from 2 to 10 and see what our report looks like now…

Value Above Replacement: It's VERY sensitive to overall demand.

Mahomes Falls All the Way from 1st to 14th! And ALL WE DID was invite eight
more friends to play with us (rather than just me versus you)!

“How did that happen?”

First, look how much smaller the values are for RB [Repl Lvl].  For RB, it used to be 331.2, and now it’s 178.1 – a nearly 50% reduction.  That makes sense because, with ten people in the league, each needing 2 RB’s, our replacement level RB is now RB #21 as opposed to RB #5.

But replacement level for QB fell by MUCH less!  [Repl Lvl] for QB used to be 352.96, and now it’s 300.22, which is only about a 15% fall.

This occurred for TWO reasons.  First, for replacement level, we only “dropped down” to the 11th QB, since we each only need 1, whereas we dropped all the way to the 21st for RB’s, since we each need 2.

BUT it also happened because the dropoff curves are quite different for the two positions.  Check out these two charts to see what I mean…

Two Overlaid Power BI Line Graphs Demonstrate the Opportunity Cost Curves for Two Different Categories

The Dropoff Curves Have Different Shapes for QB vs. RB, and Even the Slopes Themselves Vary!
(Each curve has regions in which it is steeper – or flatter – than the other)

Think of all the things we did NOT change!

I think this is kinda amazing and worth letting it just “wash” over you for a bit:  All we did was increase OVERALL demand for players, in a smooth and proportional manner!

  • Raw player values were all unchanged – everyone still has the same raw fantasy points score as before.
  • Intrinsic player prices were all unchanged – each player still “costs” your pick whenever it’s your turn.
  • The ratios of players required were unchanged too – we used to require twice as many RB’s as QB’s, and we still do.
  • But then the dropoff curves found in the real world interacted with the one change we DID make (the number of participants), and boom – our VAR rankings shifted dramatically.
I find that absolutely captivating.  Judge me if you’d like, but I dare say I find it hot, in fact.

Serious Question:  Does this apply to your business at all?

The idea of Value Above Replacement originated – as far as I know – in baseball.  Major League Baseball has access to a near-infinite pool of replacement level players, multiple positions to choose from, and ALSO a very “linear” team dynamic (meaning a team’s value is very close to the sum of its parts – just like in fantasy football, but NOT like real football, in which team chemistry, style of play, quality of coaching, and a host of other factors make a BIG difference).

So if you’ve read the book Moneyball, you know all about VAR.  And if you have NOT read the book – even if you’ve seen the movie – I highly recommend picking it up.

So VAR is a big deal in games/sports, but how often does it have application in business?  My hypothesis is that it’s pretty rare that it’s applicable in a mathematical form, while simultaneously quite relevant in everyday intuitive decisionmaking.

Anyway, I sincerely want to know, so drop me a comment and let me know if VAR has applicability in your biz ok?

OK, now let’s get to the REAL reason for this article… Winking smile

A love letter to my fantasy football-playing colleagues at P3…

Dear colleagues – Ryan/Evan/Mat/Brad in particular – and anyone else out there playing fantasy football in the 1 QB format…

I’d like to plead my case.  And I’m bringing data – I’d expect the same from you if the tables were turned, particularly with such an important topic at hand.

Here is my belief:

  1. 1 QB starter formats needlessly minimize (in fantasy football) the most important position in real-world football.
  2. 2 QB starter formats correct that (by adjusting VAR), but that’s not the only benefit.
  3. 2 QB formats also open the doors to diverse team construction – it’s possible to win with different roster compositions, whereas in the traditional format, we all know what the championship team is going to look like ahead of time.
  4. Additionally, 2 QB formats promote trading, by adding an additional valuable asset to the picture (ex: in 2 QB, it is 100% plausible to trade a QB for an RB straight up).
  5. Last but not least:  2 QB formats are FAR less exploitable by the “Rob Algorithm.”  More on this later.

Potential Objections

As far as I can tell, a reluctance to switching stems from some mix of the below:
  1. Maybe it just doesn’t work.
  2. Maybe it’s not as much fun.
  3. It’s untraditional and doesn’t mimic real football
  4. Maybe we run out of decent QB’s and not everyone can
    start 2 each week.
  5. Maybe Rob has insider knowledge about 2 QB strategy,
    and he’s hoping to exploit everyone else’s

I will labor to address each of these 🙂

1 QB Criminally Undervalues Football’s Most Important Position

Let’s start here:  Patrick Mahomes had, in 2018, the single greatest fantasy QB season in ALL OF HISTORY.  Restated, he had the all-time greatest performance – EVER – while playing the single most difficult and important position in football.  If we re-drafted 2018 again, knowing all the outcomes in advance, where would game theory tell us to draft him?  First, let’s make our model mirror our P3 Adaptive league precisely.

In our P3 Adaptive league, we start a combination of five total RB/WR via the “Flex” position, rather than 2 RB / 3 WR as modeled above.

So I’ll set both RB and WR starters to 2.5 as a crude first approach and see what happens.


Best Season Ever at the Most Important Position:
Only Good Enough for 14th-Most-Valuable-in-2018 in our Format

We all know that’s silly.  The game shouldn’t work that way.  But if we ran it back, taking Mahomes before the middle of the second round would put you on a sub-optimal path.  Truly.

So rather than being similar to real football, 1 QB format is incredibly DISsimilar to real football.  That’s not intended to be snarky, either.  Winning is the goal, and the data tells us that QB’s are incredibly unimportant.  It doesn’t get more crucially factual than that.

Let’s go a bit deeper and see how the various positions are represented in the overall top 20…


Mahomes is ONLY QB in the Top 20.
(Good thing he had the Best Season Ever, Otherwise He’d Be Sir Not Appearing on This List)

2018 wasn’t exactly a “down” year for QB’s either.  Leaguewide 2018 had the highest-ever number of passing TD’s, and the highest overall QB rating of any NFL season.  (Only 3rd highest for passing yards though).

Maybe my “2.5 of each” for RB/WR is skewing this, so I’ll re-write some formulas to treat them as a single position known as “Flex.”


Treating RB/WR as Interchangeable:  Same Number of Each Position, Mahomes Still 14th
(But quite a few swaps of position within the list, so it DID have an impact)

OK But What About the Bench?

Yep, we DO have reserve players on our rosters, so my approach of using “first non-starter” as the replacement level is flawed.  We have to go deeper.

So I’ll introduce another parameter…

[BenchFactor] = 1.5

And change my variable for NVal in my Replacement Value measure…

Power BI DAX Variable for NVal:  Modifying Our Input to Our Replacement Level Measure

So now, instead of looking at the 11th QB and the 51st “flex” player, it will look at the (1*10*1.5)+1 = 16th QB and the (5*10*1.5)+1 = 76th flex player.

Drum roll…


Moving On Up!  Mahomes to 13.  But basically unchanged – the same 20 names just in slightly different order.

Let’s See What 2 QB Does

Let’s see what happens when we change the number of QB starters to 2…


Mahomes to #1, and Roughly Even Split Between QB/RB/WR in Top 20.
(Bumped down into Round Three:  Folks like James Conner, James White, George Kittle, and Mike Evans)

That’s closer to what you’d do as a real NFL GM today, right?  Yeah, real GM’s wouldn’t have probably any RB’s in the top 20, but RB’s are fun, and I think there’s such a thing as too MUCH realism.  Which brings me to the next point.

Realism Objection

Besides, realism isn’t the chief advantage of the results above – a more interesting GAME is.  And there’s zero doubt in my mind if we were designing fantasy football from scratch today (rather than inheriting it from cocktail napkins in 1980s dive bars), we’d design it to look like the above.

But if we must persist with the realism argument, and we take the objection of “there’s never 2 QB’s on the field,” well…

There’s ALSO rarely 2 RB’s on the field anymore, and NEVER three, but I routinely trotted out 3-RB lineups last year.

I’ve also never seen a Texans QB throw 2 pass TDs in a game and have 2 of them go to a Pittsburgh player (and a third to a KC player, impossibly!), but that’s what happened for me in week 16.

The realism objection was the reason why PPR (point per reception) was slow to catch on back in the 90’s and 00’s, but here we are using it today – because it makes the game better.  A zero yard catch isn’t worth the same as a 10 yard run in real football – not even close – but it’s slowly become the dominant format because it’s a better game.

“It Won’t Work” Objections

Well, I’ve been playing 2 QB for nearly two decades now.  I wouldn’t still be doing that if it sucked.  In full disclosure, I kind of knew how this analysis was going to turn out, because I performed this same analysis, in traditional Excel, in the early 2000’s, before switching my friends and family league over.

That said, you do NOT want to run a strict 2 QB format with 12 or more people in the league.  Ten is the max, because any more than that, and the free agent pool DOES become too thin for QB’s.

But at ten players, it works out.  As long as your bench isn’t obscenely large, it just doesn’t pay to hoard QB’s.  Someone is ALWAYS dropping a valuable QB.

And if you’re in a 12-person league, or in a 10-person and worried about the switch, consider adding a “Superflex” starter spot rather than going full 2 QB.  Superflex is a spot that allows QB/RB/WR/TE – which is basically a 2nd QB starting spot since QB’s do score the most “raw” points, but if you find yourself in a pinch, you can swap in another player rather than taking the zero.  My 10-team leagues still just use 2 QB, but I think a single Superflex spot is actually a superior approach.  (I myself must admit…  I’ve fallen victim to tradition here because we didn’t have Superflex as an option on fantasy sites when I first adopted 2 QB).

“Rob Will Have an Edge” Objections

I play 2 QB with friends and family.  The skill level in those leagues is quite a bit less competitive than what I face in our P3 Adaptive league.  And my winning percentage is LOWER in the 2 QB leagues.  (True, true….  how could it NOT be lower, considering my P3 Adaptive track record – the one snarky joke I allowed myself, please forgive!)

Now, real talk?  When I first introduce 2 QB into my leagues years ago, I WAS hoping it would give me an edge, because my group of friends and fam had started to catch on to “the algorithm” and I wanted to confuse them while developing a new algorithm for myself.  As it turned out, it did more damage to ME than to them.  But the game we got was more fun, so even I was happy about it.

When the game becomes more dynamic, with more ways to win, it’s just fundamentally harder to “algorithm” your way to victory.  I’ve now been playing fantasy football for more than half my life – long enough to have Ed McCaffery on my fantasy teams, and then to have his SON Christian on my teams these past two years.  The quasi-intuitive “algorithm” I play by has been long in the making, but it is significantly less effective in the 2 QB format.

2 QB is fundamentally friendlier to casual players while simultaneously rewarding real-world football savvy.  I have no idea, for instance, whether Odell Beckham’s transition to the Browns will help or harm his production.  I similarly don’t know how the various shuffling of offensive lines in the league this offseason will impact team and player fortunes.  I don’t spend ANY time researching that stuff, and even if I did, I’d have no idea what to do with it.

Saying it differently, I never have ANY idea who is going to be “good” this year.  When one of my acquisitions pans out, it’s not because I saw it coming.  At worst, it’s dumb luck.  At best, it’s because my “algorithm” for the game CONCEDES that I have zero clue, and tries to harness the upside of chance and variability while minimizing its downsides.

So if you’re savvy enough to have an opinion on which players will “break out” this year, and act on those opinions, you’re way ahead of me.  But that sort of real-football, predictive wisdom is even more valuable in the 2 QB format, because “algorithmic” players like me are hamstrung by the extra degree of freedom it introduces.

Of course, casual players ALSO lack informed opinions on those same topics.  In his very first year of fantasy football, my brother won our 10-team, 2-QB friends and family league.  There is ZERO chance he’d have done that in our 1 QB P3 Adaptive league.  We’d have eaten him alive.

Since our P3 Adaptive league is going to have a lot of new blood this year, 2 QB would present a more welcoming vibe.  And those of you with more actual football savvy might actually beat me for once.  (OK, two jokes).

Microsoft’s platform is the world’s most fluid & powerful data toolset.  Get the most out of it.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.