DAX – “Normalize” Your Measures for Fun and Profit!

September 26, 2013 at 1:57 pm

You could divide by initial value, so everything starts at 100% and goes up our down from there.
Better, divide by initial value and subtract one, so everything us starting at zero and you’re plotting % change.

September 26, 2013 at 2:22 pm

OOH! Rob LIKE! Rob LIKE! 🙂

September 26, 2013 at 3:46 pm

First off….excellent topic. A dual axis chart can get you into trouble. And with 3+ measures of different magnitude, you are doomed. Normalized (or “Indexed”) charts are the way to go.

So….I agree with Jon relating to where the index should start. Traditionally (in my small world), the index begins at the begining of the time period observed. That way, all measures start at “1” (I prefer this over zero….sorry Jon). The best examples of these are google finance charts (Which, of course, start the index at zero) https://www.google.com/finance?chdnp=1&chfdeh=0&chdet=1380225600000&chddm=97750&cmpto=INDEXDJX:.DJI;INDEXSP:.INX;INDEXNASDAQ:.IXIC&cmptdms=0;0;0&q=INDEXDJX:.DJI,INDEXSP:.INX,INDEXNASDAQ:.IXIC&ntsp=0&ei=R45EUuitHcebqwG0ZQ

September 27, 2013 at 8:13 am

So, on the one hand, this is a clever approach — I really like the idea of independently normalizing all the data _first_, and then plotting that, rather than letting Excel decide what mins, maxes, etc. to use. (I especially like the idea of being able to explicitly define my own mins and maxes algorithmically. I’ve seen approaches that use VB to control them, but with this approach, you can just define them through formulas.)

OTOH, this approach does have one huge qualification: It’s only useful when you can afford to ignore the exact values that you’re plotting. For the comparative analyses you’re doing here, it’s perfectly appropriate, but for any application where you’re focused on the precise data points — such as temperatures in an experiment, or stock prices — then you still really need to have your axes (and axis _lines_) to reflect “true” values, rather than these scaled proxy values.

September 27, 2013 at 8:51 am

TOTALLY. And in fact, I was headed that way with the Numbers People vs. Visuals People thing but it started running too long.

Consider a pivot table with conditional formatting (data bars or color scales in particular) vs. a pivot chart with a “real” axis.

The PT is “numbers first with a side of visuals.” The PC is “visuals first with a side of numbers.” Each is optimized to show one thing – numbers or visuals – but as a secondary function, can also deliver the other component (just not as well as it delivers the first).

The punchline of this theory is that I suspect that in cases where the numbers themselves are important, we may find that a PT with conditional formatting is better than a chart with a “real” axis. Not all of the time, but a surprising amount of the time.

September 27, 2013 at 8:59 am

And as a geeky techical aside — while I haven’t tried this specific approach before, I have dealt a fair amount with customizing axis scales, etc., and there’s one useful trick I’ve discovered when it comes to defining mins and maxes.

For whatever reason, charts tend to “read” better (or, at least, I think they do) when the axes begin and end on logical, modular intervals. (For example, if the min and max values in your chart are “31.6%” and “72.9%”, then having your axis scale start at “30%” and go up to “75%”.)

There are too many possible combinations for me to anticipate and define the formulas here, but it basically always comes down to an exercise in rounding up and down, using modular arithmetic. For example, using my hypothetical data above…

MinActual = 31.6%
MaxActual = 72.9%
Modulus = 5

you could define those axis values with the following formulas…

MinScale =INT(MinActual/Modulus )*Modulus
MaxScale =(INT(MaxActual/Modulus )+1)*Modulus

The integer quotient of 31.6 divided by 5 is 6, so when you scale that back up by 5 again, you’ve basically just shaved 31.6 down to the nearest integer that’s evenly divisible by 5.
To define the max, you do the same thing, but you add 1 to the quotient before you multiply it back up, so instead of rounding 72.9 down to 70, you’re padding it up to 75.

For any internal intervals — like tick marks, or tracking quantiles — if you want them to just fall on your modulus, then calculating them is pretty easy. You just keep adding the modulus to your MinScale. If you wanted to use a different interval than your modulus (for example, if you always wanted to have 10 intervals, no matter what the max and min), then you would use something like “IntervalCalc = (MaxScale – MinScale)/10”.

September 27, 2013 at 2:30 pm

LairBob –

I wrote up a couple more general techniques for calculating axis scale parameters. They calculate the tick spacing too, which is then used like your modulus to calculate the upper and lower limits.

Calculate Nice Axis Scales in Your Excel Worksheet
Calculate Nice Axis Scales in Excel VBA

September 27, 2013 at 3:24 pm

Jon – after seeing this, the first thing I did was go look at the Axis Options, hoping that Min and Max were refedit controls.

And… they are not. So unlike the “normalization” stuff, the min and max axis values have to be manually entered, no matter how sophisticated the calculations are.

So Jon, how hard (if at all) have you pushed MS, over the years, to turn those text boxes into refedits? Seems like a natural advancement.

Gah, does this mean I’m starting to care about advanced chart scenarios?? Shit 🙂

September 27, 2013 at 11:37 pm

Rob –

It’s taken 15 or 20 years to finally get data point labels linked to cells. We’ve been asking to link axis scale parameters to cells for longer than I’ve been an MVP, so since before 2000. We’ve been asking for some kind of NULL() or BLANK() function so we can get a gap in a line chart.

They do keep tweaking the dialogs, but they seem to get harder to use efficiently. More and more clicks to do routine things, so maybe they’re trying to get us to forget about real enhancements.

But it’s possible to use VBA to update chart axes when the worksheet recalcs, as I described in an ancient tutorial, Link Chart Axis Scale Parameters to Values in Cells (VBA).

October 2, 2013 at 10:26 pm

Does it make sense to express each series as a z-score and confine every measure to one axis of deviations from each series’ mean +3+2+1+0-1:2-3?

October 2, 2013 at 10:53 pm

That sounds fancy. It goes over my head, though :). Maybe Jon is still monitoring this comment thread and will weigh in, too.

October 2, 2013 at 11:14 pm

Its close to what you were doing…take the series and find its average and standard deviation. for each element, subtract the average and divide by the sd, compressing the series into deviations from the mean. The mean is expressed as zero on the y axis, so each series can be evaluated by how far away a score is from its mean value in SDs, deviations from the mean, not the score’s absolute value. (it hurts my brain to see 250% 🙂
Absolute values could be displayed too, so the z score is the line chart and the data labels show absolute values, but that might be confusing…
Jon’s classes taught me everything I know about charts, best training money I ever spent.

October 3, 2013 at 7:48 am

Rob: Yeah, I’m getting notifications.
Drew: This is a viable approach for science and engineering, but I don’t think I’d use it for financial data, because most finance people aren’t familiar enough with statistics terminology.
Instead I’d divide by initial value and plot as a percentage, or divide by initial value, subtract 1, and plot as percentage change.
And thanks for the plug. When/where did you take my class?

October 3, 2013 at 8:33 am

I use z-scores when disparate magnitudes need to be displayed on the same axis, e.g., total emergency room visits (big number) displayed by practice size and the percent of a practice’s patients actually going to the ER (tiny number) to find proportionately high ER utilization.
My instinct is that you’re correct in that
1) small samples make standard deviations wonky
2) introducing that level of abstraction (how far above or below average is this data point instead of the difference in dollars) may be a bridge too far for the C-layer.
I attended the dashboard class you gave with Alex Kerin in Westborough MA on June 2, 2010, a year after I saw Tufte present in New York on July 30, 2009. These two experiences redefined my orientation to data analysis. I learned to love Excel (I am a SQL Server resource) because you showed us so much more than dashboards. You teach ‘meta charting’, reusable techniques that directly apply to your our own work, not isolated examples, indeed, your tutorials are nonpareil. Tufte informed my taste and aesthetic, but you showed me how to do it, and for that I thank you.

October 3, 2013 at 9:46 am

Drew you sound like an absolute monster. And I mean that in a good way.

October 3, 2013 at 11:44 am

Thanks I think
And please allow me to return the complement!

October 21, 2013 at 8:26 am

I like it too, and I’d love it if somebody would post a formula for “divide by initial value”.

October 21, 2013 at 10:57 am

I already did a whole post on it Janet 🙂

https://powerpivotpro.com/2013/10/charts-that-are-dynamically-indexed-to-first-value/

DAX – “Normalize” Your Measures for Fun and Profit!

Look Ma! Two Posts!

Three Different Scales

A Word from the Charting Pit of Derision!

Numbers are Numbers. Visuals are Visuals. Everyone has a preference.

Get on with it!

The Formulas

Alternative Formulas! Use the Average!

Cancel reply