Charts that are dynamically “indexed” to first value

 
Auto-Index Chart:  Another awesome new Power Pivot technique

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…

"When you little excel mvp's get together you're worse than a sewing circle."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.

Don't watch me.  I can't write formulas when you watch.

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

5 Responses to Charts that are dynamically “indexed” to first value

  1. It took me awhile to understand how dividing by the first value would be of use. It also took me a few minutes to come up with a use case for dividing by max value the other day. Now, considering all three of these, I’m seeing much value in first and max. Average has been drummed into me in nearly every text book example I can remember. Mean, median, projections, curve fitting, trending, mid point, standard deviation; so much of my experience (and vocabulary) is based on controls and averaged results that thinking outside of the average box is strange. Thanks for opening my eyes to the simple fact that first, last, max, and min are valid comparisons, as well. Now I have some new dimensions to explore.

  2. Bertrand says:

    Amazing : I have been struggling on the exact same problem yesterday, and this morning, here is your post. I finally came to use the ALLSELECTED function without a column reference. Wouldn’t this alternative measure also work in your context ?

    [First Date in Range] =CALCULATE(FIRSTDATE(Calendar[Date], ALLSELECTED(Calendar))

    • Bertrand says:

      Now I get it: the FIRSTNONBLANK function is still needed in case there is no fact on the first day of the selected period. I should have written:

      [First Date in Range] =CALCULATE(FIRSTNONBLANK(Calendar[Date], [Total Sales]), ALLSELECTED(Calendar))

      • powerpivotpro says:

        I think you give me too much credit. There are so many solutions to a given problem, and I end up going with the first one that works. Over time, I refine my techniques of course. But “I found something cool” becomes “I have to share it NOW.” :)

        I think this whole charting style makes a lot less sense in cases where you have blanks/zeroes in your data. I see it most commonly used in financial charts that show the relative performance of different securities since event X happened. And that sort of data never has blanks – there’s always a price for a stock, every day. In other words, the advantages of FIRSTNONBLANK might not be super-relevant here.

        For the record I think I also tried ALLSELECTED(Calendar) but only in one of my intermediate attempts while Jon was watching me. I was using it in [Sales on First Date in Range] rather than [First Date in Range] – a measure that didn’t yet exist in my brain. As the post indicates, the formulas just won’t come out while someone is watching :)

        • gbrueckl says:

          you may also use this calculation to further simplify it:
          Sales on First Date in Range:=
          CALCULATE(
          [Tota Sales],
          CALCULATETABLE(
          FIRSTDATE(‘Calendar’[Date])
          ALLSELECTED(‘Calendar’)
          )
          )

          FIRSTDATE() returns a table which can be used to directly filter the [Total Sales] to the sales on the first day
          to remove the current filter created by the X-Axis we can use CALCULATETALBE() in combination with ALLSELECTED() as already suggested by Bertrand

Leave a Comment or Question