skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile


 

Calculating Same Store Sales in PowerPivot

Another one from the forums

I love the PowerPivot forum over at Mr. Excel for many reasons, one of which is that it gives me useful, real-world inspiration for blog posts.  Recently, I saw someone asking about how to calculate “same store sales.”

Simply put, same store sales means comparing sales of ONLY stores that are open today and were also open at the same time last year (or last month, last quarter, etc.).

In other words, it’s a “year over year” (or month over month, etc.) comparison measure that only looks at stores that were open last year, and are still open today.

Highly Recommended:  A Stores Table

As a prerequisite, I recommend that you import or create a Stores table.  Doesn’t have to be fancy, it can even be a single column.

image

I Created This Single-Column Stores Table
via Copy/Paste

And relate it back to your Sales table.

The “Raw” Formulas

Here are the measures used to calculate the raw (unfiltered, all stores) sales:

[Units Sold] = SUM(Sales[QtySold])

[Units Sold Last Year] =
CALCULATE([Units Sold],SAMEPERIODLASTYEAR(Calendar[Date]))

(Note that this could be calculated MANY different ways, including using the Greatest Formula in the World).

[Raw Growth vs last Year] =
IF([Units Sold Last Year]=0,BLANK(),
   ([Units Sold]-[Units Sold Last Year])/[Units Sold Last Year] )

Transaction Count

The way I decided to determine if a store was open last year was to determine if it had any transactions.  This is slightly more reliable than testing for [Units Sold] > 0, since in rare cases you might have returns (refunds/exchanges) that offset all of the products you sold.

[Transactions] =
COUNTROWS(Sales)

[Transactions Last Year] =
CALCULATE([Transactions],SAMEPERIODLASTYEAR(Calendar[Date]))

If this measure returns 0 or BLANK for a store, I can assume that store was not open last year.  There are alternatives of course, and I may delve into those in a future post.  But this is a great place to start.

Filtering Sales to Stores that Were Open Before & Remain Open Today

OK, if both [Transactions] > 0 AND [Transaction Last Year] > 0 for a particular store, that means it’s a store that we want to “count.”

So we use that to generate new versions of Current Sales and Sales Last Yr:

[Current Sales – Stores Active Today and Last Yr] =
CALCULATE([Units Sold],
   FILTER(Stores,
         [Transactions Year Ago]>0 && [Transactions] >0
   )
)

[Sales Last Yr – Stores Active Today and Last Year] =
CALCULATE([Units Sold Last Year],
   FILTER(Stores,
         [Transactions Year Ago]>0 && [Transactions] >0
   )
)

Make sense?  We filter the Stores table to only include rows (stores) for which both current transactions and last year transactions are > 0.

Final Measure

Now it’s really straightforward:

[Same Store Sales vs Last Yr] =
([Current Sales – Stores Active Today and Last Yr]   – [Sales Last Yr – Stores Active Today and Last Year] )
/[Sales Last Yr – Stores Active Today and Last Year]

image

Growth Looks Much Worse When We Just Compare Same Store Sales!

So the raw growth and same-store growth measures tell very different stories.  In this case, that’s due to the way I manufactured data for this example (using functions like RANDBETWEEN). 

In the real world of course, this can happen when you open a bunch of new stores – skewing your overall numbers higher.  In that case, same store sales can show you that per-store sales have been falling, a fact that is masked by the broader totals. 

There are other ways to do this as well.  At Pivotstream we often account not just for stores opening and closing, but also which stores stocked which products, whether prices increased, etc.  But fundamentally it’s still the same idea.

What do you think?  Any particular variations you’d like to see?

Download the workbook here.

***UPDATE:  Version That Respects Store Open and Close Dates

If you’d prefer to use precise open/close dates for your stores rather than “did we have sales,” see the follow-on post.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 10 Comments
  1. Great post and timely for myself. I have been working on a Y/Y SSS calculation for a project I’m working on, but I’m hitting a bit of brick wall. Here is what I’m trying to do… Each store in my list has a start date. I want to use the stores start date to determine if it should be included in the same store calculation or not. Y/Y SSS calculation regardless if there are sales for that store this year or last for the specific date range I’m looking at (typically by month for last 13 months). The other twist to my SSS calculation is the store must be open for at least 15 months before it is included in my SSS Total. I hope my question makes sense. Thanks for any guidance you or anyone can offer.

    1. In my experience, that error is fleeting. If you click off of the pivot, then click back on the pivot, and try to upgrade again, it works.

      It’s weird how it fails the first time then succeeds the second time but that has been my experience.

      1. I have tried clicking off the pivot table and back on, being in the pivot table, not in the pivot table…nothing seems to work. Am I supposed to have access to some server somewhere?

      2. I tried this in Excel 2010 and received the same error, and was able to see the data model using your trick. I am still not able to see the data model in Excel 2013.

  2. How could you calculate CAGR or CQGR? This formula works fine – [SalesLast]/[SalesFirst] , but I have tried to add in the end of the formula “^(1/4)-1” to new formula = [SalesLast]/[SalesFirst]^(1/4)-1 and it gives error: “An argument of function POWER has the wrong data type or the result is too large or too small. I did double check in normal excel, and it works fine and return normal values, 10-15%. Do you have any idea how to fix it?

  3. There is something wrong with your Last Year Same Store function. All of the values for Same Store Last Year and matching the Regular Last Year Totals. I don’t know how to fix this, I just know that isn’t possible and that something isn’t right.

  4. I realize that this post is quite old but I was wondering if you’d be willing to revisit this? In the 3rd from final measure you have introduced a measure called “[Current Sales – Stores Active Today and Last Yr]” within this measure you have called the measure “Transactions Year Ago” which I’m guessing is actually “Transactions Last Year”. I’m able to follow along until I get to here and then things fall apart. I have a created a Store table, and every other measure works as expected until I get here.

Leave a Comment or Question