skip to Main Content

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


Can YOU Spot the Green or the Red Line?  (Bonus Q:  Can You Tell
That the Green Line is 50x higher than the Red Line?)

Look Ma!  Two Posts!

After failing to maintain my Tuesday & Thursday posting schedule over the past two weeks (one post total, out of four scheduled), I’m BACK folks.  I mean this week didn’t stand a chance.  BAM!  Nailed it.

OK, enough self-congratulation for meeting the minimum standards I set for myself.  Moving on!

Three Different Scales

The three lines plotted on the chart above are “sourced” from these numbers:


These Three Measures Are VERY Different in their Relative “Sizes,”
Varying From 2 Digits to 6 Digits.

Data like that results in crappy charts.  Let’s fix it with some formula magic:

Converting Values to Equivalent Scales for Charting - another Power Pivot Magic Trick!

Ah, Formulas Make Everything Better.  Yep, it’s the same data, just “normalized.”

A Word from the Charting Pit of Derision!

Yeah, I hear that chittering out there – the mandibles of the Demonspawn Chart Fiends are clacking out a sound that resembles “Secondary Axis!”

Read the Rest

Ranks and Exceptions that “Bubble Up” to Subtotals


One Training Session, Two (Three?) Blog Posts Inspired

Every now and then, I find myself training someone who I just KNOW is going to be teaching ME things in the near future.  The questions they ask are the dead giveaways.  And the next time we meet, they will be Darth Vader, telling my Ben Kenobi about how the circuit is complete.

I had the pleasure of teaching someone like this quite recently.  He forced me to write some pretty nifty calcs, or at least some that *I* think are nifty.

The Formula

Read the Rest

Moving Averages Controlled by Slicer

Two Month Moving Average in Power Pivot Six Month Moving Average in Power Pivot


Two Month Moving Average is Pretty Smooth.  But Six Months is Smoooooother.
(Imagine Barry White Saying That:  “Ohhh Yeaahhh…  A Six-Month Moooving Average.  Smoooooth…”)

It’s already been a couple weeks back (yikes!), but I recently wrote a post on simple moving averages in Power Pivot.

One of the questions, in the comments, was how to control the “length” of the moving average dynamically:



What’s That???  Oh No!  That’s DISCONNECTED SLICER’s Music!!
(Yes That’s a Reference to [link removed due to 404] Pro Wrestling Entrance Music, A Fascinating Read)

Hey, when TWO people ask for something, and one of them deploys CAPITAL LETTERS in the effort, and then pairs said uppercase with one of my favorite words “(“enhance”), well, I’m hooked.

Read the Rest

Simplifying Time Calculations and the User Experience using Disconnected Slicers

Guest Post by Jeff Lingen [LinkedIn]

How does PowerPivot fit in an enterprise BI environment

We don’t even know what it is yet. We don’t know what it is. We don’t know what it can be, we don’t know what it will be, we know that it is cool.

Zuckerberg’s early assessment of Facebook was a lot like how I felt after first discovering PowerPivot 3+ years ago. I knew it was cool but had no idea how it would fit into an enterprise business intelligence environment. For a long time PowerPivot for me was just a cool thing that I used for my own data analysis or for proto-typing tools that I would eventually turn into “enterprise-level” solutions. Today I need a pretty compelling reason not to use PowerPivot for almost all of my organization’s analytic requirements. So where does PowerPivot fit into the enterprise BI environment and how do you get associates engaged and use it to provide value?

Read the Rest

PowerPivot vs. Power View: What’s the Difference?

I’ve been getting this question a lot lately:  How does Power View relate to PowerPivot?  Is PV a replacement for PP?   (And why does PV have a space in it while PP does not?)

(For more information on the various “Power *” offerings from Microsoft, see also the newer posts What is Power BI? and The Three Big Lies of Data).

First:  Understand that PowerPivot is Kinda Two Things

Let’s rewind all the way back to Office 2010, a world in which PV does not exist.  (For most of you, we call this time Today.  And for those stuck on 2007 or 2003, you may refer to this as Tomorrow.  Or maybe even the Day After Tomorrow).

In that world, which is where this blog largely lives, it’s helpful to reflect that PowerPivot has two parts:  the PowerPivot window, and the Excel window.  They have the following relationship:

PowerPivot's Relationship to Excel


Read the Rest

Adding a Minimum Threshold Slicer to “Stores That went negative” Technique

Thursday’s Post “Fixed” The Number of Negative Stores for a Month at 8.  Now We Vary That Threshold That With a Slicer.  PowerPivot is Amazing :)

Thursday’s Post “Fixed” The Number of Negative Stores for a Month at 8.
Now We Vary That Threshold That With a Slicer.

Let’s take Thursday’s post and extend it a bit.

In the picture above you’ll see that I have 5 selected as my threshold on the new slicer, and 48 months “qualify” for that threshold – there are 48 months where at least 5 stores were negative.

Now let me select 9 on the threshold slicer:

Raising the Threshold to 9 Weeds Out 10 More Months, Only 38 Months Exhibited 9+ Negative Stores.  Did i mention that PowerPivot Rocks? :)

Raising the Threshold to 9 Weeds Out 10 More Months, Only 38 Months Exhibited 9+ Negative Stores

How’d I Do This?

Read the Rest

Toggling Conditional Formatting On/Off via Slicer

PowerPivot Makes it Easy to Toggle Conditional Formatting On and Off via Slicers

The One and Only “Sam Rad”

imageAt the Microsoft MVP Summit last week in Seattle/Redmond, I got to see and hear a lot of cool things.  But none of that compares to seeing old friends and colleagues – some of the most extraordinary people I have ever known (and ever WILL know).

People like Sam Radakovitz for instance, aka “SamRad.”  A veteran Excel team member who briefly left to do other things but is now back on Excel.  This is Very Good News.

(Actually this is a theme – a lot of former Excel program managers in particular have “come home” from other places and have rejoined the team.  I expect this next release of Excel to be something special).

In addition to being a numbers/technical guy, Sam is very visually gifted.  He takes bland stuff and makes it sing.

We all could learn a thing or two (or a hundred) from Sam.  Even in spreadsheets, presentation quality has tremendous impact.

Read the Rest

“Initializing” Slicers with too Many Values to Scroll


Has THIS ever happened to you?  We’re not even out of the Aarons yet.

Simple and Slick

Yesterday I saw Ken Puls post a really simple and effective trick – intentionally introducing a BLANK() measure in your pivot just to add a spacer column or row – and my response was “simple and slick, I like it.”

Well today I’m in Michigan, conducting some PowerPivot training/consulting, and that makes today a great day for a similarly slick and simple trick.  Because hey, I’m not even here right now.  Who’s writing this post?? Smile

First Initial!

Over in the Customers table in the PowerPivot window, add a simple calc column:

Read the Rest

One Slicer to Control Them All…


One Slicer That "Controls" Other Slicers.  Multiple slicers "get set" when the user makes one click.

Got this question yesterday from the DJ Monkey…

Let’s say you have three slicers:  Percentile, Quarter, and Category:


For Bikes, You Always Want to See the 70th Pctile in Q2

And you find yourself selecting the same combinations over and over again.  Above, you selected 70, 2, and Bikes – because Quarter 2 is essential for Bike sales, and 70 is the percentile you are most interested in for bikes.  (Note that this percentile drives conditional formatting in this example – see this post for details).

When you look at Accessories, you always want to see Q1 instead, and set the Percentile threshold to 60.  And for Clothing, it’s a different set as well:


But for Accessories, You Always Want to See the 60th Pctile in Q1


And for Clothing, It’s 80 and Q4 That You Always Want to See

Is that too repetitive for you?  Three clicks with a brief pause in between each makes you feel like a monkey?  There’s no satisfying you, is there? Sheesh Smile

But hey, there’s a way to essentially set multiple slicers in one click, without VBA, as long as you know ahead of time what the desired combinations are.

Read the Rest

David Hager on Dynamic Conditional Formatting

Intro From Rob:  Greetings from Vegas!

Well ok, Vegas isn’t tons of fun when you don’t leave your hotel room very much, but hey, I have a great view of castles and downtown Manhattan.  I’m a little worried that this photo may open a wormhole into some alternate universe however:


Hey Wait a Second – How Is this Picture ALREADY in
the Blog Post Being Written in The Same Picture???  My Head Hurts.

Anyway, today David Hager has graciously stepped to the plate with a guest post.  He’s seen me goofing around with conditional formatting a lot and he’s got some advanced techniques to add to the mix.  Today’s post sets the stage for that.

It all revolves around the capability of CF (Conditional Formatting) to use formulas as the “decider” of whether or not to format a cell:


This CF Rule Type is Important to David’s Techniques

All right, take it away Mr. Hager…


By David Hager

There has been a lot of demos and discussion on this blog relating to the innovative use of conditional formatting (CF), particularly in connection with PowerPivot.

The technique of CF in Excel is especially powerful where formulas are used to define the desired TRUE/FALSE condition to be applied. It occurred to me that in this era of data visualization and controls (such as slicers) for altering the data view, there was no user method for changing the underlying logic of applied conditional formats. As an example, say that on a worksheet that cell D7 has a CF formula of =D7>8. There is no way to change the CF to =L7>8 unless the CF control on the Ribbon is opened and the formula is manually changed. Further, a CF formula with both conditions ( =AND(D7>8,L7>8) ) may be what is desired, and the user may not know how to do this. Thus, I decided to create a method that allows for the selection and changing of CF conditions from dropdowns using data validation lists.

Read the Rest