By Avichal Singh

As part of the microsoft.com/learning team we release products throughout the year –courseware, books, exams and eLearning (check out MVA for tons of free courses). We often need to understand how our products perform after launch and how they compare against each other (in the first month since launch, first quarter, first year etc.). For Example: we would compare the various courses we launched around Visual Studio 2010. We may compare Visual Studio 2010 against Visual Studio 2012 courses. We may even compare Visual Studio against SQL Server. Or compare adoption by geography or customer segments.

I can imagine similar need for other businesses, e.g. a car manufacturer who needs to compare performance of various year, make and models.

Power Pivot and Power View can allow us to go from View 1 below, which is inscrutable at best, to View 2 which really helps us understand and differentiate the adoption ramp of various products. In this article, I would explain how you can go from View 1 to View 2 using the car manufacturer example.

View 1: Monthly Sales by Car Model
Typical view available in BI, but not very insightful

Power View Graph Monthly Sales by Car Model

 

View 2: Cumulative Sales since Launch, by Car Model
Clear view into adoption ramp of various products

Power View Graph Cumulative Sales since launch by Car Model

Note: The Rocket Ship, Hot Burner and Slow Burner analogy inspired by Tableau’s Tale of 100 visualization. It is interesting that we often do see our products cluster in three layers as shown, when we are analyzing our actual product performance.

Sections

Preview Data Set
Step 1: Create Dimension Table to show days since launch
Step 2: Create cumulative measure in fact table
Additional Considerations

Preview Data Set

We have the model as shown below. With the Sales fact table connected to the dimension tables: Cars, Date and Geography. This can generate the View 1 as shown above. Find a link to the workbook at the end of this post.

Power Pivot Data Model Diagram

Step 1: Create Dimension Table to show days since launch

First problem we would solve is that products were launched at different points in time. We need to line them up, so for each, we can see the performance in Month 1 after launch, Month 2 after launch and so on.

Need to align the start point of various product launch
Power View Graph need to align product launch

Add DaysPastLaunch column to fact table

We add a new column in FactSales table as below (make sure to change data type to Whole Number), which gives us the days since launch.e.g. if [LaunchDate] for that specific Car is Jan 1st 2010 and PurchaseDate in FactSales table is Jan 2nd 2010, then DaysPastLaunch would show 1 day (past launch).

DaysPastLaunch =

[PurchaseDate] – Related(Cars[LaunchDate])

Create DaysPastLaunch table

Since we need flexibility in being able to analyze in month, quarter or years after launch we define a pseudo Date table with various attributes. The difference being, here each month is 30 days since we did not want the variability in number of days in a calendar month to affect our analysis. The final table looks as below.

 DaysPastLaunch a pseudo date table
image

Create relationship between fact and DaysPastLaunch table

Next create a relationship as shown

Power Pivot Relationship

Results after Step 1
Line graphs are aligned at the same start point…but the month to month variability still makes it confusing
Power View Graph after Step 1

Note: More on the (Blank) Year Month at the end of this post.

Step 2: Create cumulative measure in fact table

Instead of showing sales Quantity by Year Month past product launch, let us show the cumulative sales Quantity since the product launch. For that we create the formula as below. Based on the cumulative total dax pattern, slightly modified, to show blank when there is no sales Quantity.

SumCumulativeQuantity :=
IF (
    ISBLANK ( [SumQuantity] ),
    BLANK (),
    CALCULATE (
        [SumQuantity],
        FILTER (
            ALL ( DaysPastLaunch ),
            DaysPastLaunch[DaysPastlaunch]
                <= MAX ( DaysPastLaunch[DaysPastLaunch] )
        )
    )
)

Code beautified with DAX Formatter

That small change, from quantity to cumulative quantity, renders a beautiful graph, which lets us clearly see how our various products are performing.

Cumulative Sales since Launch, by Car Model
Clear view into adoption ramp of various products

Power View Graph Cumulative Sales since launch by Car Model
You can also view adoption across other dimensions, like Geography or Customer segment
Here we see that East and Central region show the best adoption for Alto car model.
Power View Graph Cumulative Sales since launch by Geography

Additional Considerations

There may be additional considerations which you would have to contend with. I touch upon them here, but would not fully cover them for the sake of brevity (laziness?).

Dealing with (Blank) DaysPastLaunch

You would notice in graph above that we have values where DaysPastLaunch field (Year Month past launch) shows (Blank). In the data set, we see transactions where the purchase date is prior to launch date. These transactions are not matched up successfully to the DaysPastLaunch table hence they show up under (Blank).

Days Past Launch can be negative
Days Past Launch can be negative

Now either this is a:-

– Data issue and some data cleanup and validation is in order (perhaps Launch Date is earlier than as recorded)

– Valid scenario, perhaps involving preorders of a product. It may be best to extend the DaysPastLaunch dimension table to account for negative values as well.

Grouping products

In our contrived dataset here, we are contending with only Year 2010 models launched by our manufacturer.  What if we had data for multiple car categories and year of manufacture?

Power Pivot how to handle groups of products

Note: You may notice that some Year 2010 models have a launch date in 2011 and 2012. Let us assume that these were 2010 models that were terribly delayed due to safety considerations.

How would we show comparison between groups of products. Say:-
– Sedan versus Coupe versus Hatchback
  Or
– All ‘Year 2010’ Models versus all ‘Year 2011’ models

The Power Pivot model as built above, would actually work and show you the requisite graphs. However most likely you would want to build additional safeguards so data is used and interpreted correctly.

Tread carefully when showing groups of products by DaysPastLaunch
Power Pivot how to handle groups of products

Safeguard #1: Account for differing number of products
Different product groups may contain different number of products (e.g. perhaps we launched 10 Sedans but only 1 Hatchback car). You may address that by dividing sales Quantity by number of active products, thus getting sale Quantity per unit product.

Safeguard #2: Product group may have products with different timelines
E.g. the Sedan category may contain cars that were launched in 2010, 2011, 2012 and 2013. If you group these together you would get misleading results. Best way around is to add additional dimension which indicates the age of a product. Then filter down to products which are past a certain age, say only show products that are at least one year old.

Here is the link to the workbook: ProductPerformanceSinceLaunch.xlsx