How’d I Do This?

Nice Pivot, But I Only Want to See Months Where Eightor More of My Stores Went Negative!

***Update: Technique Extended, Workbook available

In a followup post I have added a slicer that lets the report user control the minimum number of stores required, rather than fixing it at 8 like this post does. Also, the workbook is now available for download.

Tales from Remote Consulting

Awhile back I left my job to start a new company. I’m not yet ready to announce what that new company is about – I’m working hard on that and you folks will be the first to know. Spoiler: it’s about PowerPivot and Excel.

But in addition to hard work, there’s also a lot of waiting involved in all of that. I’ve been filling the gaps with training and remote consulting to keep my head in the PowerPivot game.

Remote consulting in particular is a lot of fun – people send me a workbook, I spend 1-3 hours and build what they want, then send it back. Gives me a good sampling of the problems that are “out there.”

One of those remote consulting jobs featured the problem pictured above (except that they had real data, and what I’m showing is 100% fake).

How Many Stores Fell Below Zero Each Month?

Excel MVP Forever. PowerPivot Pro On the Rise!

Back in December I wrote about Ken Puls’ role in inspiring the book, and described him as a DAX convert (and also someone who used to intimidate me, in a good way, at MVP Summits back when I was a newbie on the Excel team). Well I’m happy to welcome a guest post from Ken today.

I think it’s particularly valuable to hear from a) someone who is still relatively new to the PowerPivot journey like Ken and b) someone other than me, period – since both provide a very different perspective, and that helps us learn.

So, take it away Ken…

Background

In British Columbia we’ve been working with a 12% HST (Harmonized Sales Tax) for the past 1.5 years. Effective April 1, 2013, we’ll be going back to a system with a separate 7% Provincial Sales Tax (PST) and our national 5% Goods and Services Tax (GST) instead. In our case, we wanted to look at sales that will not be PST taxable under the new tax structure, meaning that the effective tax on these sales will drop from 12% to 5%.

So assuming that we have the following tables in an Excel worksheet and the name of the tax table is tblTaxRates, it’s really easy to get the effective tax rate for any date:

We simply add a VLOOKUP to the sales table with the following formula copied down the sales table:

=VLOOKUP([@Date],tblTaxRates[#All],2,TRUE)

Easy stuff for any Excel pro. But what do you do if your sales table is in PowerPivot, like this?

Why am I doing this in PowerPivot? Primarily as a challenge.

This is a question I should have answered before I even started down this road.

To be honest, I did it primarily as a challenge – to stretch my brain a little bit. If I were faced with this exact same task in my daily work, undoubtedly I would just use normal Excel formulas. In some ways, this modeling exercise has been a deliberate misuse of PowerPivot. A handful of parameters with no source data whatsoever – this is NOT what the PowerPivot engine was built for, which explains why the PowerPivot solution is actually significantly more difficult than the Excel solution.

“So you’ve been deliberately wasting our time??”

No, I do think there is real value in this exercise, for two reasons:

Brain-stretching with new techniques always comes in handy later. For instance, on the first post Sergey commented that he’d been thinking about loan amortization measures and this could be applied to that.

I can see this technique being added, as a supplement, to a broader PowerPivot model. For instance, a model containing lots of real customer data over time, and then a [Projected Customers] measure that forecasts future customer populations based on various assumptions and/or marketing investments.

So with that in mind, here it is: the final installment of viral/marketing modeling in PowerPivot.

A Tale of Two Charts

Let’s say you operate a business that relies heavily on “word of mouth” – customers recommending your product/service to their friends and colleagues. Or at least, you THINK it relies heavily on that sort of thing.

You need to decide how much to spend on traditional advertising – to supplement the social/viral marketing that your customers do on your behalf. Take a look at each of these two charts – the captions for each attempt to capture the knee-jerk conclusions you might draw:

“Advertising? We Don’t Need No Stinking Advertising!
That is SO Yesterday! We’re Viral Baby!”

“All These Youngsters and Their ‘Viral This’ and ‘Social Media That’ – That’s All Just Fancy Excuses to Be Lazy – You Clearly Need to BRING Your Message to the Customer”

If chart 1 reflected reality, you may opt to spend very little on traditional advertising. But in a chart 2 world, you’d be silly to rely on viral growth. But which one (if either of them) describes your situation?

Back in October, Rahul Vohra (CEO of Rapportive) wrote a two-part blog series on this topic, posted here on LinkedIn. I took a note, at the time, to revisit his work and “convert” it to PowerPivot.

It’s a very different kind of problem from what I normally do in PowerPivot – this isn’t about analyzing data I already have, but about calculating future outcomes based on a handful of parameters. And that leads to some different kinds of thinking, as you will see.

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

Count of New Customers per Day in PowerPivot

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

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.

If You’re a CPA, and Need CPE Credit, ConsiderWatching Me Show Off PowerPivot for an Hour

I’ve had an interesting new experience this week – I recorded a webcast for CFO Magazine. Bill does these all the time and asked if I’d be interested in doing one this month.

I’m usually game for this sort of thing, and it did turn out to be fun. Squeezing a whirlwind tour of PowerPivot as well as a bunch of specific how-to techniques into a single hour – there’s a certain pace to that which I kinda liked. I had to be thrifty with what I showed.

(I suspect Bill is a lot faster at recording these than I am however – I spent probably 20 hours recording a one hour webcast.)

Anyway, two key points:

The webcast is not free. It is certified as training for accountants and there is a $149 fee for the session, so unless you are a CPA, I suspect you are going to skip this one.

It includes a copy of my book. The $149 fee for the course does not go to me – that goes to the CFO Mag organization. But you do get a copy of the book, and yes, they do pay me for the book.

A Post on Thanksgiving?

Normally I would take today off and not have a post. But I’ve posted so many updates about the book lately that it’s got to feel like this place has turned into an advertisement shop, and I want to keep the “real” content up.

So consider this a Thanksgiving “bonus” post – me giving thanks for everyone ordering the book, and everyone tolerating my desire to post updates about “my baby” every five minutes

A Missing Function

It’s funny, I’ve never used NETWORKDAYS() much (if at all) in regular Excel, so I didn’t realize this until someone posted on the MrExcel forums – how do I do a NETWORKDAYS()-style calculated column?