New Customers per Day – Technique by David Hager

January 8, 2013

Hi folks.  Today we are fortunate to have a guest post from David Hager.  He explains a technique for counting how many new customers are acquired or “seen” each day.  (I’m going to think about whether this has web site traffic analysis uses as well – New Visitor vs. Returning Visitor sort of stuff).

***UPDATE:  Inspired by David’s work, I extended this technique to cover per Month, Year, Week, etc.:

Count of New Customers per Day in PowerPivot

By David Hager

Information vital to any company is being able to identify customer patterns. Counting how many new customers per day a company acquires is perhaps the most important data that can be obtained. The following model will show how this can be done with DAX measures in PowerPivot. For comparison, two other measures are included in the Pivot Table (shown in Figure 1).



Note that COUNT(Table1[CustomerID]) would return the same result.



This measure returns the number of unique customers.


=CALCULATE([DistinctCustomersPerDay],DATESBETWEEN(Table1[Date], BLANK(),LASTDATE(Table1[Date])), All(Table1[Date]))
CALCULATE([DistinctCustomersPerDay],DATESBETWEEN(Table1[Date], BLANK(),LASTDATE(Table1[Date])-1), All(Table1[Date]))

This formula shows the real power of DAX. The first part of the formula (highlighted in green) returns the running total of the DistinctCustomersPerDay measure. The second part of the formula (highlighted in yellow) returns the running total of the DistinctCustomersPerDay measure up to the previous day of the pivot table row context. The difference affords the number of new customers per day.


Read the rest of this entry »

Free Site Signup: Wave One Open

November 7, 2012


***UPDATE – FULL:  Wave one filled up fast, no need for 48 hours.  We actually went over 100 during the night and hit about 130.  We’re letting all of those in, but are taking down the signup form now.

Stay tuned for news about Wave Two Smile


Excel Pros Don’t Always Dress Like This of Course, But It’s Still a Great Picture

Following up from yesterday’s post, here’s the link for the free site signup:

<Link removed, wave one full>

Bring it on Smile

Minimal Handholding is Intentional

One of our goals here is to see how understandable our whole system is, so we’re trying to keep “human handholding” to a minimum and see where/if people stumble.

Go watch yesterday’s video if you haven’t already.

Then there are support forums available to you once you have your site.  See if you can find them.  I’m intentionally NOT telling you HOW to find them Smile

That said, if you have outright “I can’t go any farther and am completely stumped” problems and can’t get to the forums for some reason, hit me up with a mail.  I’m rob.  At a place called powerpivotpro.  Dot com.

Cloud PowerPivot to Launch Free Offering

November 6, 2012


***UPDATE – FULL: Wave one filled up fast, no need for 48 hours. We actually went over 100 during the night and hit about 130. We’re letting all of those in, but are taking down the signup form now.

Stay tuned for news about Wave Two Smile


If Ebenezer Scrooge Were Alive Today, He’d Use PowerPivot.  And He’d Love This Post.
(Believe it or not this is an original image I commissioned ten years ago)

Taking my “High Priest” Role VERY Seriously

There are a few themes that I just keep hammering on, month after month.  Most of those revolve around the stunning new future opening up for Excel Pros.  I believe every thing I say about that stuff.  It is REAL.

One of the biggest and most transformational changes is this:  taking your workbooks to the web.  Workbooks were being emailed around back when Roxette topped the worldwide music charts.  PowerPivot workbooks published to a server are a very, VERY different experience, one that inspires MUCH more respect from the person consuming them.

Short Version: Free Forever for Lighter Workloads

This week, Pivotstream is launching something that I’ve been dreaming about for a long time: a way for you (yes YOU, dear reader) to harness the power of PowerPivot server (publishing workbooks as interactive web apps)… for free.

Not a trial. This is more of a Dropbox-style model where lighter usage is completely free, and you only pay if you want more capacity.  I want to remove any barriers I can so that you can experience what I’m talking about (without bankrupting my company of course), and I think we’ve figured out how to do that.  But before I get into details, let me show you something.

Just Add Upload

Thanks to a recent focus group I recruited here on the blog, I learned that many people expect there to be some sort of intensive conversion process – it seems like you would need to put a lot of work into a workbook before it becomes an interactive web application like the ones on our demo site.

So I recorded a video showing that it’s much, MUCH simpler than that.  Just upload Smile

Upload and Share – Short Video Illustrating an Even Shorter Process Smile
(I recommend watching in HD and Fullscreen)

Benefits to Excel Pro

I didn’t have time to cover this in such a short video, so here’s a quick table comparing the old way to the new way, through the eyes of you, the Excel Pro:

Read the rest of this entry »

PowerPivot V2 – To The Cloud!

August 2, 2012

Power View Report From PowerPivot V2 (2012) Workbook Running on Pivotstream Cloud Platform

My First Real Experiment with Power View – Built From a PowerPivot V2 XLSX!
(Running on our New V2 Cloud PowerPivot Platform)

Lots of Work Pays Off

The #1 question I hear every day is “when will Pivotstream offer support for V2 on their cloud platform?”  And in fact, that’s maybe the #1 question that I ask of the team every day, too.

There’s a lot of demand for it, given all the new bells and whistles in the V2 release.  But we’re no longer a fledgling little operation – we can’t just upgrade everyone overnight.  Actually, we could, but that would be irresponsible – we have to make sure none of our customers get burned in the process, and huge software releases like V2 have a tendency to be…  finicky.  If we upgrade everyone and things start breaking, saying “it’s Microsoft’s fault” is not an answer – we have to hold ourselves to a higher bar.

So our V2 cloud platform is a completely separate and parallel investment – new hardware, new domains, new base URL’s, etc.  A lot of time and money, in other words.  So if you’ve been wondering “what the heck are they waiting for,” now you know.

In Private Beta Now, Sign Up for the Public Beta

Read the rest of this entry »

Dynamic TopN Reports via Slicers, Part 4 – The Dot Plot PivotChart

July 19, 2012

Guest post by Colin Banfield [LinkedIn]

After completing the Part 3 extension of Rob’s Dynamic TopN Reports via Slicers, Part 2 post, I did not plan on a forth installment. However, I did plan to write about creating dot plot PivotCharts sometime in the future. Later, it occurred to me that the TopN reports model provided the perfect foundation upon which to create dot plot charts.

The dot plot is not a standard chart type in Excel, so most users haven’t heard of, let alone used one. The dot plot is an alternative to a horizontal bar chart, and there are many situations where it is a better fit for analysis. For a good introduction to dot plots, see this excellent article by data visualization expert, Naomi Robbins.

The following summarizes some of the potential advantages of the dot plot over a bar chart:

  • When there are a lot of category items on a chart, a bar chart can look cluttered. Because a dot plot uses less “ink” to represent the same data, the resulting chart tends to be less cluttered.
  • The dot plot is often better than a stacked bar chart. The values in a stacked bar chart can be hard to compare because only the bottom bars have a common baseline.
  • Depending on how the dot plot chart is organized, you can gain better insights than using a clustered bar chart (see example in Naomi’s article).
  • Since the absolute length of a bar chart encodes its value, the value axis must start at zero. If the values in the chart are all a distance from zero, you can’t make good use of interval values on the value axis. On the other hand, the dot plot values are judged by position along the axis – length is not involved. Therefore, you can have more optimal intervals on the value axis.

    FIgure 1 shows dot plot PivotCharts based on the data used in Part 3.


    Figure 1 – Dot plot PivotCharts (Click figure for an expanded view)

    Read the rest of this entry »

  • Dynamic TopN Reports via Slicers, Part 3

    July 17, 2012

    Guest Post by Colin Banfield [LinkedIn]

    After Rob posted Dynamic TopN Reports Using PowerPivot2!, I downloaded the workbook from the provided link to examine how his “tricks” were done. Shortly thereafter, I sent a message to Rob complementing the techniques he used, and mentioning the potential for using the techniques in other scenarios – especially those that include dynamic charts. I didn’t realize that I was setting myself up, because Rob asked if I’d be willing to write a post detailing one of these scenarios. For the sake of continuity, it makes sense to treat the scenario that I will be discussing as an extension of  Dynamic TopN Reports via Slicers, Part 2. You should have thoroughly read and understood that post before you continue here.

    The additions that I have made to Rob’s TopN reports are as follows:

  • Created BottomN measures for Customers
  • Created TopN/BottomN measures for Products
  • Added a Year-Month slicer

    Created TopN/BottomN charts for customers and products that react to slicer selections for TopN, By (selected measure), and Year-Month range.

    The final result of these efforts is shown in Figure 1.


    Figure 1 – TopNBottomN charts (Click figure for a wider view)

    Read the rest of this entry »

  • Dynamic TopN Reports via Slicers, Part 2

    July 5, 2012


    How Was This Done?

    OK, picking up from Tuesday’s post, with the goal of explaining the techniques.

    And remember, you can download the workbook here!

    Read the rest of this entry »

    Dynamic TopN Reports Using PowerPivot V2!

    July 3, 2012

    Dynamic TopN PowerPivot Report, User Can Select a Measure and a Value for N

    Check out THIS Bad Boy!

    Old Tricks Combined With New

    Folks, the combination of slicers and DAX is just…  amazing.  Do you SEE that picture above? 

    If the consumer of the report wants to change it completely, and see Top 10 Customers by Total Paid (Sales), it’s two quick slicer clicks:

    Dynamic TopN PowerPivot Report, User Can Select a Measure and a Value for N

    How’d I Do It?

    Read the rest of this entry »

    DAX – Making the “Case” for SWITCH()

    June 5, 2012

    Guest Post by Colin Banfield [LinkedIn]

    Way back during the period of the first CTP of Gemini (which later became PowerPivot), I was working with a data set that included a column of month numbers. I wanted to create a calculated column with month names, and the only solution that I could think of at the time was using the IF function in DAX:

    =IF([MonthNum]=1, “January”,
    IF([MonthNum]=2, “February”,
    IF([MonthNum]=3, “March”,
    IF([MonthNum]=4, “April”,
    IF([MonthNum]=5, “May”,
    IF([MonthNum]=6, “June”,
    IF([MonthNum]=7, “July”,     
    IF([MonthNum]=8, “August”,
    IF([MonthNum]=9, “September”,
    IF([MonthNum]=10, “October”,
    IF([MonthNum]=11, “November”,
    IF([MonthNum]=12, “December”,

    This a horrendous formula, with eleven nested IF functions and a long tail of closing parentheses. I promptly made a suggestion in the Connect forum for the addition of a “Case” function in DAX, siting the preceding formula as justification for its inclusion. Sometime later, I received a message from Howie Dickerman indicating that some form of Case function was being considered for PowerPivot V2 (the current version). This “Case” function turned out to be the SWITCH() function. “Switch” is a function familiar to Access and VBA users, so it made sense to stick with that name for the DAX  implementation.

    Read the rest of this entry »

    KPI’s in PowerPivot V2

    May 31, 2012

    PowerPivot KPI's in V2:  What Are They and How Do We Use Them?

    A Shiny New Dialog to Play With!

    Better Late Than Never!

    On Tuesday, in my intro to David Hager’s post, I promised to circle back “later today” and add some follow-on thoughts.  Well, ONE of those words ended up being truthful Smile

    But I’m back today!  Let’s not dwell on the past shall we?

    In David’s post, he ended up with a “TestStatus” column set to Pass/Fail/Warning depending on a) what the test value reported  and b) the specific acceptable ranges established for that particular kind of test.

    He was then able to use that as a slicer.  But what about conditional formatting?  His original example, a regular (non-pivot) table, DID include conditional formatting.

    This is where I thought it would be good to finally check out the new KPI feature in PowerPivot V2.

    What is a KPI?

    It stands for Key Performance Indicator.  Essentially though, what it means in practice is “a measure that has conditional formatting built into it.”

    Pretty cool concept really – imagine being able to define your conditional formatting rule once and then use it any pivot with a single click!

    Read the rest of this entry »

    Conditional Formatting with Different Thresholds per Test, per Product, per Store, per Division, etc.

    May 29, 2012

    After a long hiatus, David Hager has returned with a new guest post.  He has a clever Excel trick/formula for applying different conditional formatting “acceptable ranges” depending on the context of the current row.  In his work, different Tests have different acceptable ranges of values that qualify as Pass/Fail/Warning.


    Each Test Has a Different “Pass Range” and “Safe Range” –
    David Hager’s Technique Translates This Table Quickly into CF Rules

    I think this technique can be extended to basically anything:  an acceptable sales growth figure for Store A may be different than that for Store B (or Product A vs. Product B, etc.)

    His post also got me thinking about the new “KPI” feature in PowerPivot V2, so I will return later today with a brief follow-on post.

    Read the rest of this entry »

    Percentile Measures in DAX – Errata

    May 22, 2012

    Guest Post by Colin Banfield [LinkedIn]

    In September of last year, I posted two articles on creating percentile measures in DAX.  See Creating Accurate Percentile Measures in DAX – Part I and Creating Accurate Percentile Measures in DAX – Part II. About three months after I posted Part I, Richard Mintz left a comment indicating that he was having trouble getting correct results when his data sets had a wide range of values and many duplicates. I haven’t been receiving notifications when comments are left, so it’s purely by chance that I saw Richard’s comment recently.

    During the process of building the measures, I did do some testing with duplicates, but the testing was minimal and involved only duplicates at the 25th or 50th percentile mark.

    To check out the reported issue, I built a new data set with many duplicate values. Figure 1 shows the results of the percentile calculations in this scenario:

    Percentile Measures Update Figure 1

    Figure 1

    Huh? What’s going on here? The 50th and 75th percentile calculations are correct, but the 25th percentile calculation is totally and utterly incorrect! I created several intermediate measures along the way to the final result, so it was trivial to track down and correct the problem.

    Read the rest of this entry »