skip to Main Content

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],
                        [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]),

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.

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 10 Comments
  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. 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))

    1. 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))

      1. 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 🙂

        1. you may also use this calculation to further simplify it:
          Sales on First Date in Range:=
          [Tota Sales],

          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

          1. For some reason, it does not allow me to have ALLSELECTED with only one argument. However, Bertrand’s formula works… So many mysteries in this Microsoft product :/

  3. Firstly, this is an awesome measure!

    But I think I’ve spotted a slight problem when you’re normalizing values with multiple categories within categories.

    What this measure appears to do is it first sums the value and only then indexes afterwards. So what can happen is if you have two series running from 2000 but one starts off as a blank while the other starts off with a small value, then in 2001 when the other series kicks in with a huge value, it gets summed and then indexed, so the indexed valued skyrockets.

    What could potentially be a massive improvement to this measure (in my opinion), is if it was able to index first on whatever data slice, THEN average the indexed values afterwards for visualization in a line chart. That way the index won’t get skewed if there’s a few missing data points, or very large categories in their own series that shouldn’t mixed until after it has been indexed.

    Would love to here any thoughts

  4. Any clue how to make this work in Power BI?

    In Excel’s Power Pivot, [First Date in Range] nicely returns the first date of a month. But the same formula in Power BI returns the date of a row, therefore [Normalized Sales] is always 1 in Power BI.

    1. I added in:

      IF([Total Sales]=0, BLANK(),[Total Sales])),

      To make sure days with no data points are ignored by FIRSTNONBLANK.
      Seemed to do the trick.

Leave a Reply

Your email address will not be published. Required fields are marked *