skip to Main Content

Power Pivot, Power BI, and Microsoft Excel techniques for Accounting and Finance Professionals.

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

Showing Only Months/Weeks/Etc. When at Least N Stores Showed a Certain Behavior

 
image

Nice Pivot, But I Only Want to See Months Where Eight
or 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.

Find both in the followup post, located here.

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?

Read the Rest

Guest Post from Ken Puls: Determine Effective Tax Rate

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… Smile

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:

tax-1

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?

Read the Rest

Modeling Viral and Marketing Growth, Part 3 of 3

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:

  1. 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.
  2. 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.

Read the Rest

Modeling Viral and Marketing Growth, Part Two

 
Picking up from last week’s post, the first thing I want to show is that I kinda cheated last time.  To see what I mean, let’s look at Rahul’s original chart:

Viral Marketing Growth in PowerPivot:  Customers Flatten Out Over Time

In Rahul’s Viral Model, Total Customers “Goes Flat” Quickly

In Rahul’s model, if we start With 5,000 initial customers and a viral factor of 0.2, we end up with 6,250 customers and we never get any more!

But in my model from last week, if I use 5,000 and 0.2, customers keep piling up exponentially:

Exponential Ongoing Viral Growth in PowerPivot

In My Model from Last Week, Customers Never Go Flat –
They Just Keep Growing Exponentially

So why the difference?

Read the Rest

Modeling Viral Growth and Marketing 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:

 
Modeling Viral Growth versus Traditional Direct Advertising in PowerPivot

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

Modeling Viral Growth versus Traditional Direct Advertising in PowerPivot

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

 

Read the Rest

New Customers Per Day Generalized to “New Customers per Month,” etc.

 
 
A Generalized New Customers (or unique visitors) in Time Period - per Month, Year, Etc. in PowerPivot

A Generalized “New Customers in Time Period” Solution, Inspired by Tuesday’s Post

David Hager’s post on Tuesday really planted a seed in my brain.  And then a comment on that post from Charlie got me thinking further.

How can we extend the “New Customers per Day” concept to become “New Customers in <Any Period of Time>?”  New Customers per Month for instance.

Read the Rest

New Customers per Day – Technique by David Hager

 
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.:  http://powerpivotpro.com/2013/01/new-customers-per-day-generalized-to-new-customers-per-month-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).

TotalCustomersPerDay:

=COUNTROWS(Table1)

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

DistinctCustomersPerDay:

=DISTINCTCOUNT(Table1[CustomerID])

This measure returns the number of unique customers.

NewCustomersPerDay:

=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

CFO Magazine Webcast on Monday

 
image

If You’re a CPA, and Need CPE Credit, Consider
Watching 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:

  1. 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.
  2. 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.

What I Cover in the Webcast

Read the Rest

NETWORKDAYS() Equivalent in PowerPivot?

 
There is no NETWORKDAYS() Function in PowerPivot

There is no NETWORKDAYS() Function in PowerPivot

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 Smile

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?

Something like this:

NETWORKDAYS in PowerPivot

Desired Result

So how do we get to this?

Read the Rest