DAX : Calculating monthly sales changes

Introduction

In my former blogpost about PQM analysis I've talked about the PQM analysis and how you can use the SUMX function to calculate the revenue by multiplying two columns : Quantity and Price. In this blogpost I want to calculate the monthly changes in sales.

Current situation

I've created a simple Calendar lookup table for analyzing the Sales data with a Date Column that is different than I'm used to built in data warehouse project. In data warehouse project you create a surrogate key like 20150908 and use this as a PK -FK relation between two tables. In PowerPivot you join on a normal dates like 8-9-2015. This is a best practice!

In the Sales data table I've created a date column as a calculated column (not field) and based on this column I join the SalesPeriodDate column with the calendar table (DateKey).

=DATE(Sales[Year]; Sales[Period];"01")
 

In the following diagram a representation of the join is presented:

I've already created a Sales Calculated Field with SUMX (see my former post):

TotalSales:=SUMX(Sales; Sales[Price] * Sales[Quantity])

The next step is creating a previous month calculated field, SalesPrevMonth :

       
SalesPrevMonth:=CALCULATE([TotalSales];DATEADD(Calendar[DateKey]; -1; month))
 

This resets the current filter context to the previous month and this results in the following data:

And here you can see the current month sales compared with the previous month. Now, We can calculate the variance in the monthly sales compared to the sales of the previous month.

       
 %CurrentPrevMonthSales:=DIVIDE([TotalSales] - [SalesPrevMonth]; [TotalSales])

Now let's take a look at the percentage of monthly changes :

And here you can see the monthly changes in percentage. I didn't like the 100 % in the month January and therefore I included a test whther SalesPrevMonth is empty. I adjusted the DAX expression a bit:

       
%CurrentPrevMonthSales:=IF(
                          NOT(ISBLANK([SalesPrevMonth]));
                          DIVIDE([TotalSales] - [SalesPrevMonth]; [TotalSales])
                          )

And now the result is this:

And now the calculation is disappeared for the column January. I can also calculate this per product ( I removed february and August):

Conclusions

With simple calculations you can have powerful insights.

Greetz,

Hennie 

DAX : The SUMX function

Introduction

When do you  need the SUMX() function? Easy question but more difficult to answer. One of my current projects is about analyzing the sales data with the PQM analysis. What is PQM analysis, I hear you say? Well, PQM stands for Price, Quantity Mix analysis or sometimes it's called PVM analysis: V for Volume. And one of the calculations that needs to be done is calculating the Sales amount based on Quantity Sold and Price per Unit. S

In this blogpost I'll show you an implementation of the SUMX function.

Example data

Below, I've created an example in Excel and I've read it into PowerPivot for calculating the Sales amount. Here we have some categories, Products, Periods with Quantity and Price.

The first step in the PQM analysis is calculating the SalesAmount. 

SUMX Function

The next step is loading the data in PowerPivot and calculating the Sales Amount. So I added the data to the model in PowerPivot and calulate the SalesAmount with the following DAX Formula:

       
 Sales:=SUMX(Sales; Sales[Price] * Sales[Quantity])

SUMX is an iterator and this means that SUMX iterates over the table, in this case the Sales Table and it calulates a formula: Sales[Price] * Sales[Quantity]. So, it calculates for every row Price * Quantity. Below an example:

Sales Product A : 1 * 100 + 2 x 85 + 3 x 100 + 2 * 90 + 2 * 85... = 1640

Below, the result of this calculation in the Pivot table:

And the SUMX function calculates the Sales amount also on other attributes:

The beauty of PowerPivot, automate the data analysis and still very flexible.

Conclusions

This is the first step in the PQM analysis. In this blogpost I've used the SUMX function for calculating the Sales amount with expression Price x Quantity per row. The SUMX function is an iterator.

Greetz,

Hennie

Excel : Creating a Date dimension in Excel (for usage in PowerPivot)

Introduction

Currently experimenting with PowerPivot and I needed a date dimension in PowerPivot. I used to create this in SQL Server but I decided to build this in Excel. On BlueGraniteAcademy there is a splendid example on how to create this. I just followed this example and below you can see my implementation of this. 

Date dimension in Excel

So, In order to use a date dimension in Powerpivot we have to use a source for this. In this case I would like to use Excel for this. Why, beause it's easy to create in Excel, you can keep all your data together with the other data in excel and I needed it for some demos.

We need to create to the following table:

We have a couple of columns:

• Datekey : =YEAR(B2) * 10000 + MONTH(B2)* 100 + DAY(B2)
• DataValue : fixedvalue eg 1/1/2012
• DayOfMonth : =DAY(B2)
• DayOfYear : =B2-DATE(YEAR(B2), 1,0)
• Year : =YEAR(B2)
• MonthOfYear : =MONTH(B2)
• MonthName : =TEXT(B2, "mmmm")
• QuarterOfYear : =INT((MONTH(B2)-1)/3)+1
• QuarterName : ="Q" & H2

The next step is to drag the line downwards until the point you want to reach. In my case 31/12/2015.

The next thing is to select the complete table of the date dimension and press CTRL - T and the followig window appears.

Press OK and rename the table by clicking on Table Tools Design and editing the TableName Field

Go to the PowerPivot Tab and press on Add to Data Model. PowerPivot Should look like this:

Conclusion

This is a simple example of creating a DateDimension for PowerPivot. Perhaps in the future I'll add more fields to this implementation.

Greetz,

Hennie

DAX: The RELATED() Function

Introduction

Today, a blog about the RELATED() function of DAX (Data Analysis Expressions) in PowerPivot. The RELATED function is a function that combines a field from another table into the table where you enter this this expression. In this blogpost I'll use an example of the Contoso retail example, that you can download from the Microsoft site.

Data model

Below is the datamodel shown, where there is a 1:n relation between DimProduct -> DimProductSubCategory -> ProductCategory. So one product can have one ProductSubCategory and multiple ProductSubCategories can belong to multiple Products.These relationships are called chained relationships because they form a chain that you can follow from the beginning
to the end in order to relate many tables. And in the multidimensional (and tabular) world we recognize this as a hierarchy construction.

In the screenshot above you can see fields ProductCategory and ProductSubCategory in the DimProduct table and these are not actually native fields but related columns from the DimProductSubCategory and ProductCategory.

Even if the original data model of Contose Sales has two distinct tables for category and
subcategory (which is correct for a standard database system), the data model is much easier
to query if we integrate certain fields in the DimProductSubcategory and ProductCategory in de DimProduct dimension and hide the DimProductCategory and ProductCategory.

RELATED() function

The RELATED() function relates columns from other tables within the table where you use the RELATED() function. Below you can see the expression that is used in the Dim Product table.

And this is the expression:

=RELATED(ProductCategory[ProductCategoryName])

You can only use the RELATED() function  when a relationship is defined.

Conclusion

This is a great function for combining data from multiple tables..

Greetz,

Hennie

Installing PowerBI Desktop V2

Introduction

This week PowerBI Desktop (formerly known as Power BI designer) is released by Microsoft, in case you haven't noticed by now;-) this Desktop version is the Self Service BI stack and is the counterpart of the more IT related stack SSIS, SSRS and SSAS. This new BI suite (PowerBI Desktop) is combination of PowerQuery, PowerPivot, PowerMap and PowerView. One of  the reasons that PowerBI Desktop is a separate solution, is because of the slow release cycles of the Power tooling in Microsoft Office (because of the huge install base of Office extensive testing has to be done in order to release a new version). With PowerBI Desktop faster new versions of the Power tooling is possible.

In this blogpost I'll describe the intallation process of PowerBI Desktop. In later blogposts I'll dive deeper into the functionality of PowerBI Desktop and DAX.

Installation

1. First download the PowerBI Desktop to your system. The start the PBIDesktop_x64.msi (In my case). Then click on Run to start the installation process.

Wait until the initialisation process has ended.

2. Click on Next to begin the installation process.

3. Accept and click on Next

4. Determine the installation path and enter Next

5. Click on the Install button.

The installation process continues with installing the files.

6. The installationprocess has ended and now you have to click on the Finish button.

PowerBI is starting up now

The starting window is opened and ready for usage.

Conclusion

First impression of Power BI Desktop are great. Looking forward to work with PowerBI Desktop
in projects....

Greetz,
Hennie