Getting started with Microsoft Power BI using Google Merchandise store data.

          While I’m as loyal to Excel as the next analyst, when it comes to data visualization and interactive dashboards, Tableau is my tool of choice. If I need to analyze large data sets, I prefer to get cozy with the data by writing SQL queries in whichever database environment such data might be stored. In the meantime, the world does not stand still, and Microsoft has been making substantial progress with a product offering they called Power BI . In fact, this tool offers data preparation, data discovery, dashboarding and custom visualization features starting with a free version for up to 1 GB of stored files and a modest $10 monthly plan for the beefed up PRO version. It’s definitely long overdue, but I finally got around to playing with both: Power BI desktop and cloud-based versions, all while using publicly available data from nonetheless, but Google’s merchandise store. , available through their demo Google Analytics account. Which other etailer can boast growing their Cyber Monday sales by 274% to $54K, while keeping their marketing advertising budget under $ 100?

          Similar to other BI providers Microsoft’s experience seems to be best optimized for downloading and using a desktop version of the program and then sharing your results by publishing a final dashboard on the cloud. From first glance user interface offers a plethora of different options that one wouldn’t necessarily expect from a $ 10/month subscription, yet alone a free product. In fact, while I will definitely need to explore these features in greater detail, Power BI appears to be more than adequate Business Intelligence tool, while having a very sleek appearance:

1. Waterfall Chart – Variance by Category.
          We are now faced with an interesting problem – what drove such a significant uptick in sales revenue? The first question we could try to answer is determine which product categories drove this performance improvement? A waterfall chart can prove to be useful. Unless your have MS Excel 2016 or subscribe to Office 365, you won’t find a native support for such graphs in your Excel environment, which is yet another reason to get up and close with Power BI:

DAX formulas until today, but I’m very impressed with the promise this language offers so far, here is an example of a very simple YoY custom field calculation I needed to create to make my chart possible:

 YoY_R = CALCULATE(SUM(Report_Sales[Revenue]),Report_Sales[Year] = 2017) - CALCULATE(SUM(Report_Sales[Revenue]),Report_Sales[Year] = 2016)  

I simply needed to be cognizant of my table and field names.
          If you are still not convinced about using Power BI just yet, consider a million-dollar drill-down capability. With one plain right-mouse click we can see which products specifically are responsible for a sharp decline ($2,436) in Apparel product sales.

2. Treemap Chart – Category Revenue by 2016.
          Another new to Excel 2016 chart type was a treemap graph, a great pie chart alternative. If we wanted to visually see product categories driving our revenue, this is the chart we could consider. After all, it only takes a moment to see that Nest and Apparel were responsible for more than half of store revenue last year:

3. Bar Chart – Revenue By channel YoY.
          It would also be interesting to see which marketing channels drove ecommerce revenue for Google’s merchandise. It’s hardly a secret that this company enjoys a very loyal fan base, not to mention it’s massive workforce. In fact, by plotting digital channels side by side for this year versus year performance during Cyber Monday, we can easily observe that a sharp decrease in Direct to Site channel was not offset by an improvement in store’s SEO campaigns. It does appear that Google started leveraging it’s internal email and other corporate (Googleplex) referral resources to drive online sales for it’s merchandise storefront:

4. Donut Chart – Top 5 Products by Units Sold.
          Another new(er) chart type available in PowerBI is a donut chart. If you wanted to compile a top 5 products sold online by units sold, this chart can serve this need nicely (just make sure that you limit number of categories to 2-5 range.) Earlier this summer Microsoft added a much needed Top N filter option to Power BI’s interface. We realize that laptop stickers selling for under $ 1 would probably yield different volume of sales than $ 350 Nest alarm systems:

          How are you using Power BI and what are some of your New Year’s resolutions?