Charts that are dynamically “indexed” to first value

Try Doing THIS in Normal Excel:  A Chart That is Indexed to Always
Start at 100%, Regardless of Time Frame Selected
(Yes it’s an ugly chart, but a beautiful technique)

One Idea Leads to Another, and Another…

I love this kind of thing.  Last week I posted about measures that are indexed/normalized to make the scales match.  The two techniques I suggested were “divide by max value” and “divide by average value.”

Jon Peltier, Excel MVP and Visualization Guy, dropped in on the comment stream with some of his frequently-used techniques, such as “divide by first value.” 

I was flattered to see Jon show up here – I’m a Formula Guy, he’s a Chart Guy, and we’re not supposed to fraternize (kidding about that part).  In all seriousness, our paths do not cross very often.  We struggle sometimes to find common ground.  But now, we have discovered our point of overlap.  (Cue ominous music, “the world may never be the same again,” etc.)

Portable Formulas Are a MAJOR Help to Chart People!

His suggestions really got me thinking.  My internal monologue was basically “divide by first value, that’s awesome!  But I bet that is a royal PITA to set up in normal Excel, particularly when you want to look at different time frames.”

It was time for a Demo.  I had to show Jon what Power Pivot, and the portable formulas therein, could do for him.

So we did a web demo session.  Jon was excited by what he saw, but I will let him tell you that.  He’s working on some new “hybrid” techniques and I don’t want to steal his thunder.  Stay tuned.

The Formula

Cutting to the chase, here’s the formula for the normalized measure displayed on the chart:

  [Normalized Sales] =

  DIVIDE([Total Sales], [Sales on First Date in Range])

OK that’s anticlimactic isn’t it?  The real magic is in that [Sales on First Date in Range] measure:

  [Sales on First Date in Range] =

  CALCULATE([Total Sales],
            DATESBETWEEN(Calendar[Date],
                        [First Date in Range],
                        [First Date in Range]
                        )
           )

Sorry, I keep teasing you.  I like to write my measures in intermediate steps like this.  The REAL magic is in that [First Date in Range] measure:

  [First Date in Range] =

  CALCULATE(FIRSTNONBLANK(Calendar[Date], [Total Sales]),
            ALL(Calendar[Date])
           )

I struggled, at first, to write that measure.  I tried using ALLSELECTED(Calendar[Date]), but since I was slicing by Month (a different column than Date), and letting Month filter the date range rather than filtering by the Date column directly, ALLSELECTED(Calendar[Date]) was returning Jan 1 even when I had selected December on the month slicer.

(Funny story:  Jon was “live” on the demo with me while I was writing that formula, and I struggled even more because I was being watched.  I suggested he go get a cup of coffee and I’d have the formula written when he returned.  He obliged, and I did indeed get it written by the time he came back.)

VALUES() provided another dead end.  I found myself desiring an “ALLVALUES()” function before realizing that FIRSTNONBLANK is built for this kind of thing.  All’s well that ends well.