Repeat Customers in DAX: Three Flavors

December 9, 2014

Post by Rob Collie

Repeat Customers in Power Pivot / DAX:  By Number of Transaction Lines, By Number of Distinct Orders, and Allowing for Cross-Year Return Customers

In 2004, There Were 2,561 Customers Who Bought Something in the Southwest.
But How Many of Those Were Repeat Customers?  Depending on How We
Define “Repeat,” We Can Get at Least Three Different Answers.

A Right Turn at Albuquerque…

I sat down today to write about “Disconnected Cube Formulas” – yes, you heard that right.  A brand new technique that I think has some pretty exciting (yet admittedly narrow) applications.

But along the way, like Bugs Bunny, I ended up doing something at least as interesting.  So let’s do that one first.

Setting Up the Problem

I have four relevant tables:  Territories, Customers, Calendar, and Sales:


The first three are Lookup (aka Dimension) tables, and Sales is a Data (aka Fact) table.

Active Customers is a pretty easy formula:

  [Active Customers]:=


Read the rest of this entry »

Displaying Top N, Bottom N, and “All Others”

November 11, 2014

Post by Rob Collie


If We Use Excel’s Built-In Top N Filter to See Our Top 1,000 Customers, It Hides the Other Customers Completely.  But Using DAX, We Can Just “Split” the Audience into Two Groups.

This Came Up Recently…

Hey, I absolutely ADORE the TOP N filter capability offered by all Excel Pivots.  It kicks major booty and we use it all the time:


The Top 10 / Top N Value Filter in Pivots:  Get to Know It, It Does Amazing Things

But If we set that to show us the top 10 customers, it shows us JUST those 10 customers:


OK cool, we see those top ten customers, and that they collectively purchased $132,026 of stuff from us.

But we want to ALSO see how much the OTHER (non top 10) customers are worth too.

Read the rest of this entry »

New Twist for Dynamic Segmentation: Variable Grain Control

November 6, 2014

By Avichal Singh [Twitter]

Dynamic segmentation or banding has been covered in PowerPivotPro articles in the past and in beautiful detail by the Italians – Marco Russo, Alberto Ferrari (these folks are literally “off the charts!” in Matt’s representation of Power Pivot skill levels :-) ).

It involves grouping your data in specific numeric bands or segments; for example looking at your sales data based on the price range of your products. You have a long list of price points for your products, instead of looking at each price point individually it would be nice to group them into segments say to represent the low, medium and high price items in your catalog.

Hundreds of products at different list prices…  =>  Grouped based on their List Price Range

Variable Range Selection

That is great, however it is hard to predefine segments that would work well in all scenarios. As your data changes over time, or as users slice and dice your existing data (e.g. filter to a specific region, product category or year) the segments may prove to be either too granular or not granular enough. In the case below, the predefined range does not have enough grain or detail and pretty much everything ends in one bucket ($3000-$4000).

Predefined Segment Ranges may prove too granular or not granular enough
as you work your data

What if your segments had a range of options from high to low granularity, so that you could choose the right segments based on the data and your need!

Range can be chosen to show 1000s, 100s or 10s based on dataset

Download File
Watch on YouTube or keep reading…

Read the rest of this entry »

SUM, SUMX or CALCULATE()…Choices, choices!

October 23, 2014

By Avichal Singh (Avi)

When I was working recently with a client, helping her remotely – I asked her to calculate the sum for sales amount in the table. She responded whether she should use SUM, SUMX or CALCULATE?

Simple question, but not a simple answer. Or I can give you the classic lawyer response – “It depends!” Let’s review.

SUM: Simple Unmitigated Magic

The good news is that a simple SUM, would work in majority of the cases. A simple sum in the hands of Power Pivot is a powerful tool. With the magic of relationships a simple SUM can show you tricks you could never have imagined in Excel.

Power Pivot relationships mean, that you define your measures once and use them everywhere.

Power Pivot Measures: Define Once, Use Everywhere“Define Once, Use Everywhere”

Your measures conform to the shape of your pivot, so you can drag and drop any fields from your model, use any slicers and the measures would still work.


Simple SUM() does magic in Power Pivot: Define Once > Use Everywhere in action

Only when you see the results and go…”Uh…that is not exactly what I want”, should you explore other options.

Step at a time – SUMX

Read the rest of this entry »

How about a FIRSTDATE()?

October 9, 2014

Guest Post by Willem van Dijk


I am, like most (if not all) visitors to this website, an addict for information –> data –> truth –> Εμπειρία <-hope that sounds like Greek to you…

My journey into Power Pivot has been one of uphill struggles and downhill thrills. It’s been brief moments of ego feeding, immediately followed by lengthy bouts of meekness. One moment I am Indiana Jones going for his gun in The Raiders of the Lost Ark directly followed by the same scene in The Temple of Doom

Where did my DAX go?

Read the rest of this entry »

Blending “Time of Day” Analysis with Calendar/Date Analysis

October 7, 2014

Post by Rob Collie
Blending “Time of Day” Analysis with Calendar/Date Analysis in Power Pivot and Power BI

Our “Morning” Website Traffic is Down 21.5% in Jan 2014 vs. Jan 2013, But “Evening”
Traffic is Up by a Similar Amount, and Full-Day Traffic is “Flat” at +0.9%

(Fake Data, But Real Analysis)

Two Different Flavors of “Time”

Usually, when we talk about “time” in Power Pivot, we’re talking about the Calendar/Date flavor:  How much have things changed from yesterday to today.  What are our Month to Date numbers, and how do those compare against the same period last year?  Let’s call this “macro-trending.”

But time of day is also often interesting:  what are the trends WITHIN a day?  Let’s call this “micro-trending.”

And then, the hybrid of the two:  how are our “micro” trends changing over the course of the year, month, quarter, etc?

I don’t think the techniques here are terribly complicated, but they might be a little difficult to conjure up on your own.  So, it’s time for a post – and a downloadable workbook! Smile

The Key:  Separate the Date and Time Components!

Read the rest of this entry »

5 Interactive Chart Techniques Come Together

October 2, 2014

by Matt Allington

Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from, from searching the Web, as well as some of my own ideas.  While some of the techniques I will talk about in this post are not new, I have combined some of these old favourites with some new techniques to solve some of the problems I have come across. I want to illustrate how the combination of these things can deliver a very positive user experience, and just as importantly – anyone with a good set of Excel skills can build an interactive charting tool like this by following the patterns demonstrated.  I have created a demo of all of these concepts into a new workbook using Adventure Works so that you can see how these techniques come together for the user.  There is a link to this workbook at the end of this post.

The techniques I have used are:

  1. Disconnected slicers used to create interactive chart series
  2. Cube formulae and standard Excel to make an interactive chart title

I love these 2 tips I learnt from Rob – so user friendly.  However I came across a few problems when I tried to implement these, hence I have developed the following 3 additional techniques to solve these problems.

  1. Cube formulae and standard Excel to make an interactive legend
  2. VBA and “link to source” for interactive axis formatting
  3. Excel VBA to change which Axis the series appears on.

I have created a short video to demonstrate the 5 features built into this workbook, and I then explain each of these in more detail below.  I have not hidden the behind the scene workings so you can see these in action – of course you would normally hide these from the user.

Now let me call out the key techniques I have used to make this workbook rock.
Read the rest of this entry »

Using SUMX on an IF statement–Call Center Example

September 18, 2014

Guest Post by Elin Ramsey

PowerPivotPro Intro: I love real world examples. They really show how the tool is being used and the challenges you may encounter in a real world situation (like not having access to all the data you need, read how Elin tackles that).

Note how Elin slowly builds this example and keeps layering business logic in the DAX formulas. This comes naturally to the Business User who is intimately familiar with the workings and what is actually needed from the data. Throw in a third party and suddenly the process becomes much more burdensome as two parties need to communicate back and forth. In fact majority of the cost of any project (including BI projects) is Communication, read more here.

Building BI Faucets Then PlumbingI will go out on a limb and say “Business Intelligence belongs to the Business User”. The BI team is necessary in building the plumbing, but Business User should firmly be in charge of building the faucets. And build faucets before the plumbing. That is one of the stops in Rob’s amazing story (read or watch) that he recounted at PASS BA Conference. Here’s to building faucets! Elin, take it away…


My name is Elin Ramsey and I work as the Analytics Manager in a call center. Click here to see more about me. I’ll be reviewing a real world application of SUMX on an IF statement, as recently described here.

One thing I need to be able to estimate is the number of active customer support representatives we have on any given day. I can’t use HR or timecard data as I don’t have access to it. Another challenge is that some representatives will sometimes be acting as trainers most of a day and we don’t want to count them then.

I’m going to walk through what my definition of active is, how we set up the data, calculating whether a representative is active on any given day, using SUMX to calculate the number of active reps on any given day, and then a couple of examples of what we can do after we have that formula.

Read the rest of this entry »

Tuesday Grab Bag of Biz and Fun

September 16, 2014

Post by Rob Collie
mullet with labels

Today’s Post is Like a Mullet Haircut:  Business in the Front, Party in the Back

Let’s Go!

Series of quick updates today, mixture of pure fun and serious biz, sometimes simultaneously.  In this post:

  1. Download the Draft Index for the Online University – whether you are currently enrolled or not, this may be relevant.  Grab it here.
  2. Live Class in Philly – seats still open for Oct 21-22, more details here.
  3. “Secret Summit” Between Microsoft and Power Pivot early adopters last week.
  4. DAX:  The Power of Paper
  5. Yours Truly taken into police custody for Possession of Industrial-Grade Data Tools


Read the rest of this entry »

CALCULATE ( ) Lessons from Looney Tunes

September 4, 2014

By Matt Allington

In my last post I talked about a mistake I made early on in my DAX learning journey.  In that post I showed a formula that used CALCULATE ( ) to turn a row context into a filter context (AKA context transition).  Here is a quick refresh of the relevant part of that post.

calculate post

Since this post, I have been thinking about CALCULATE ( ) and wondering how to explain “WHY” CALCULATE ( ) creates context transition – this is the topic of today’s post.

I am sure there is a wide age profile of readers of this blog, and at least some of you would remember the Merrie Melodies cartoon “Cheese Chasers” where the dog (Marc) is sitting and using an ACME adding machine to make sense of what he is observing. [I know I said Looney Tunes in the title, but there is not a lot of difference and I thought more people would know what Loony Tunes was]. Before you watch this brief 40 second clip from the cartoon, let me first set the scene.  Everything is backward; the mice don’t eat cheese, the cat WANTS to be chased by the dog – you get the idea.   Spoiler alert – I will refer to the punch line below, so don’t read on until you watch the video if you want the full 1951 immersive experience.

Read the rest of this entry »

SUMX of IF: A Perfect Blend of Simple & Sophisticated

August 26, 2014

SUMX of IF Used to Make Grand Totals Add Up in Power Pivot DAX

In This Case, Getting the Grand Total Correct for Each Row Required SUMX

It’s that time of year again…

…when my love of spreadsheets actually translates into a love of sports.  Yes, it’s Compulsive Data Crunching Disease season, AKA Fantasy Football Season.

Fantasy football is a game in which the contestants assemble “portfolios” of NFL players in the same manner that you might build a portfolio of stocks and bonds.  Then your portfolio (we call it a “team”) performs well if the real-life NFL players perform well, and poorly if not.  The one difference between this and the stock market is that no two “portfolios” can contain the same NFL player – so if I get Peyton Manning, the other contestants in my league (typically 8-12 people) cannot have him.

I’m participating in a new form of league this year, one in which the contestants get to keep some of the players from prior years.  (In most fantasy football leagues, you start each year from a clean slate).

We’re going to be picking our players this weekend at an “auction” or “draft,” and naturally, I want to scout my opponents ahead of time.  Muhaha.

So, what do my opponents need?

A valid portfolio consists of:

Read the rest of this entry »

The Many Faces of VALUES()

August 21, 2014

Guest Post by Scott at Tiny LizardMany Values

Maybe it is a sign of where I am on the Geek Scale compared to Rob, but where he considers EARLIER() to be a pretty hard function to understand, it just doesn’t bother me.  At least it seems to have just one purpose in life.

Now, the VALUES() function on the other hand… well, that’s just some messed up stuff right there!  Not only does nothing about it feel natural and intuitive to me, but it also seems to behave in completely different ways depending on how and where it is used.

Basically, every time I use it, I feel like I either got lucky, pulled a fast one, and that I’m a dirty cheater.  So, at least I got that going for me.

Let’s look at some of the various usages.

Read the rest of this entry »