Add New Skills!

Udemy
Showing posts with label DAX. Show all posts
Showing posts with label DAX. Show all posts

Saturday, January 14, 2012

Microsoft BI: Defining DAX Calculations with Excel 2010 PowerPivot

DAX stands for Data Analysis Expressions is designed to deliver easy-to-use constructs that allow extending the base PowerPivot model with calculations. With DAX you can define two types of calculations: (1) Calculated columns in PowerPivot Tables (2) Measures that define new fields in PowerPivot task pane.
If you’d like to follow a step-by-step tutorial on DAX, be sure to check out the labs > Defining DAX Calculations with Microsoft PowerPivot for Excel 2010.

Let’s create some DAX calculations in this existing Excel 2010 report:

image

Go to PowerPivot>PowerPivot Window and note that we have the following tables with already extracted data from AdventureWorks SQL database:
[US Sales], [Product], [Category], [Subcategory], [Date], [US Geography], [US Population]



image

Add a new calculation DAX:=[SalesAmount] - [TotalProductCost]
Note support for intellisense in PowerPivot:

image

Create StateCode2 DAX as: =[StateCode]

image

Create a Relationship with US Population table:

image


image

Note: that PowerPivot does not support creating more than one relationship for a column. That’s why we are created a State2 DAX Calculation.
You can hide columns from showing in the Pivot Table by right clicking on the column and choosing Hide Columns>From PowerPivot:

image

Go to US Population table and create a new DAX Calculation:=SUMX(RELATEDTABLE('US Sales'), 'US Sales'[SalesAmount]) / [Population]

image 

Note: RELATEDTABLE function to retrieve all related rows based on the relationship created in the previous task. The SUMX function is then used to aggregate the SalesAmount column of the related rows. The aggregate result is then divided by the state’s population in order to compute the sales per capita.

To switch back to Excel:
image

Note that you need to click on the Refresh Button to see the new calculations:
image

Add GrossProfit to the report. In order to change the name and the formatting, right click on any cell under GrossProfit and choose “Value Field Settings”:

image

Change the name of the measure to Profit:

image

Apply a Number Format to Gross Profit to Comma Separated with Zero decimal places:

image

Lets apply some conditional formatting:

image

image

image

Now let’s add a new Measure to the PivotTable by going to PowerPivot ribbon and selecting “New Measure” and add the below DAX:=SUM('US Sales'[GrossProfit]) / SUM('US Sales'[SalesAmount]) . Note that Intellisense works inside the Measure Settings Dialog.

image

Rename Measure to Profit% and apply Percent (%) formatting:

image

Apply conditional formatting as we did earlier:

image

Looking for some hands on practice without installing the tools? Check out SQL Server Virtual Labs: http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx
Wondering what is PowerPivot, check out: http://www.microsoft.com/en-us/bi/powerpivot.aspx