PowerPivot DAX: Moving Average

Standard

A quick blog post about sharing PowerPivot DAX formula to calculate moving average. Please use this as building block for your scenario:

Here’s the Data:

DATE AMOUNT
6/1/2013 50
6/2/2013 40
6/3/2013 30
6/4/2013 20
6/5/2013 10
6/6/2013 10
6/7/2013 20
6/8/2013 30
6/9/2013 40
6/10/2013 50

(usually, the date would in a date table. For the purpose of sharing building block of this formula, I kept it this way to keep it simple)

Formula (Calculated Measure):

[code language=”text”]
Moving Average Last 3 days:=CALCULATE(AVERAGE(TableName[Amt]),DATESINPERIOD(TableName[date],PREVIOUSDAY(TableName[date]),-3,day))
[/code]

Results via a Pivot Table: powerpivot dax moving average

Note that the formula is a building block and you’ll have to make changes as per your requirement and data model. Feel free to leave a comment if you need some assistance from my side. And also consider using the PowerPivot forum to reach out to community: MSDN Forum – PowerPivot for Excel

Exploring, filtering and shaping web-based public data using Data Explorer Excel add-in:

Standard

Data Explorer let’s you “Explore” (search) for web-based public data. This is a great way to combine data that you may have in your data-sources with public data sources for data analysis purposes. Sometimes your data might not tell you the reason behind the observed trends, so when that happens – you can try to see if a public data-set might give you the much-needed context. Let me give you an Example before we start hands-on w/ data explorer so that you have better understanding of importance of public datasets. Here’s a sample that I found here. So, Here’s a demo:

An auto company is seeing sales trends of Hybrid cars and SUV’s from the sales data-sources. But what is the reason behind that? company data does not show that. Someone hypothesizes that it might be because of gas prices. So they test out the hypothesis by combining gas prices information available via public data. And turns out gas prices might be the driving force of sales trends! SEE:

if the gas prices increase, then the sale of SUV go down and the sale of Hybrids go up:

data analysis combine data with public datasets

You know that public data can be helpful! So how can you search for public data-sets? Well, You can manually search online, ask someone, browse through public data repositories like azure data market (and other data markets), there’s also a public data search engine! OR you can directly search for them via Data Explorer.

Here are the steps:

1) Excel 2010/2013 > Data Explorer Tab > Online Search > type “Tallest Buildings”

excel public data search data explorer2) I selected one of the data-sets that said “Tallest completed building…. ”

excel data from internet

3) Now let’s do some filtering and shaping. Here are the requirements:

– Hide columns: Image, notes & key

– clean columns that has heights data

– Show only city name in location

OK, let’s get to this one by one!

4) Hiding Columns:

Click on Filter & Shape button from the Query Settings:

excel data shaping cleaning

Select Image Column > Right Click > Hide:

excel hide remove columns

Repeat the steps for notes & key column.

Click on DONE

5) clean column that has heights data.

Click on Filter & Shape to open the query editor

A) let’s rename it. Select column > Right Click > Rename to Height > press ENTER

B) let’s remove the values in brackets. Select Column > right click > split column > By delimiter > At each occurrence of the delimiter > Custom and enter “(” > OK

excel split a columnThis should transform the data like this:

excel data explorer split a column

Hide height.2 and rename the height.1 to height

Click on DONE

6) Let’s just have city names in the location column

click on Filter & shape to load query editor:

A) select location > right click > split column > by delimiter > Custom – Enter: ° in the text box like this:

an excel split by delimiter dataclick on OK

Hide Location.2, Location.3, Location.4 & Location.5

Select Location.1 > Right Click > Split Column > by Number of characters > Number of characters: 2 > Once, as far right as possible > OK

cleaning data in excel shaping filtering

Hide Location.1.2 and rename Location.1.1 to Location

One last thing! making sure that the data type of height is numbers.

Select height > change type > number

Also,

Select floors > change type > number

click on DONE. Here’s our filtered and shaped data!

filter data excel shape clean

7) LET”S VISUALIZE IT!

For the purpose of visualization I copied first 20 rows to a separate excel sheet and created a chart:

z excel data visualization

That’s about it for this post. Here are some related Posts on Data Explorer:
Unpivoting data using the data explorer preview for Excel 2010/2013
Merging/Joining datasets in Excel using Data Explorer add-in
Remove Duplicates in Excel Tables using Data Explorer Add-in
Web Scraping Tables using Excel add-in Data Explorer preview:

Your comments are very welcome!

Upgrading PowerPivot Excel Add-in

Standard

Here are the steps visually:

upgrade powerpivot excelHere are the detailed steps:

Uninstall existing version:

  1. Navigate to Control Panel > Programs > Programs and Features > Uninstall a program
  2. Locate the PowerPivot add-in installed on your machineun install power pivot excel
  3. Uninstall this program

Install new version:

1. Search for “PowerPivot download”. As of now, the URL is: http://www.microsoft.com/en-us/download/details.aspx?id=29074

2. Download the add-in

Now, how do select 32 – bit vs. 64 – bit? While 64-bit is recommended, you should have the 64-bit office to do that. Either case the version of the office installed should match the version of the PowerPivot installed. So how do you check that?

Excel > File > Help

power pivot 32 64 bit excel3. Since I’ve 64 bit office, I’ll install the 64-bit version of PowerPivot. For that to happen, I downloaded the 64-bit version of PowerPivot.

4. And installed it.

Install download power pivot excel

That’s about it for this post. you can also double-check the version of the installed version of the PowerPivot: How to check the installed version of PowerPivot and what’s the latest version?

Excel 2010: How to check the installed version of PowerPivot and what’s the latest version?

Standard

How to Check the version in Power Pivot?

Open Excel 2010 > Click on Power Pivot Tab > Settings:

powerpivot settings excel

It should tell you the version number:

powerpivot version number

Now, let’s check the latest version available for download:

Search for “PowerPivot download”. As of 6/4/2013, Here’s what we get:

URL: http://www.microsoft.com/en-us/download/details.aspx?id=29074

power pivot download excel So it says 11.0.31.29.0

That’s about it for this post. In this post, we covered:

1. How to check the version of PowerPivot Installed?

2. What’s the latest version of PowerPivot available for download?

 

Data Analysis and In Memory Technologies, let’s connect the dots:

Standard

SPEED is one of the important aspect of Data Analysis. Wouldn’t it be great if you query a data source, you get your answers as soon as possible? Yes? Right! Of course, it depends on factors like the size of the data you are trying to query but wouldn’t it be great if it’s at “SPEED OF THOUGHT“?

So Here’s the Problem:

Databases are mostly disk based and so the bottleneck here is the speed at which can get access to data off the disks.

So what can you do?

Let’s put data in a RAM (memory) because data-access via memory is faster.

If it’s sounds so easy, why didn’t people do it earlier? And why are we talking about “In Memory” NOW?

1) BIGGER Data Size/sets and so today with more data, it takes more time to query data from databases. And so researchers have looked at other approaches. One of the effective approach they found is: In-memory

(And I am not ignoring the advances in Database Technologies like Parallel databases, But for the purpose of understanding “Why In-memory”, it’s important to realize the growing size of data sets and a viable alternative we have to tackle the problem: In memory. And also I am not saying that it’s the ONLY way to go. I am just trying to understand the significance of in-memory technologies. We, as data professionals, have lot’s of choices! And only after evaluating project requirements, we can talk about tools and techniques)

2)  PRICE of Memory: Was the price of RAM/memory higher than what it is today? So even though it was a great idea to put data in memory, it was cost-prohibitive.

So Let’s connect the dots: Data Analysis + In Memory Technologies:

What’s common between Microsoft’s PowerPivot, SAP HANA, Tableau and Qlikview?

1) Tools for Data-Analysis/Business-Intelligence 2) Their Back End data architecture is “In Memory”

So since Data Analysis needs SPEED and In-Memory Technologies solves this need – Data Analysis and Business Intelligence Tools adopted “In-memory” as their back-end data architecture. And next time, when you hear a vendor saying “in-memory”, you don’t have to get confused about what they’re trying to say. They’re just saying that we got you covered by giving you ability to query your data at “speed of thought” via our In-memory technologies so that you can go back to your (data) analysis.

That’s about it for this post. Here’s a related post: What’s the benefit of columnar databases?

your comments are very welcome!

A Social Media Analytics Sample Dashboard in Excel Powered by PowerPivot.

Standard

I found a great sample Dashboard on Social Media Analytics in Excel that is powered by PowerPivot. Here’s the screenshot of the Dashboard.

excel powerpivot twitter social media analytics dashboard 1

Here are the steps if you want to download and play with the Dashboard:

  1. Install Power Pivot add-in
  2. Download the “Analytics for Twitter” excel sample (powered by PowerPivot). Link: http://www.microsoft.com/en-us/download/details.aspx?id=26213
  3. It creates an “Analytics for Twitter” excel file on Desktop > Open it.
  4. The dashboard is powered by data it pulls in the Power Pivot:excel powerpivot twitter social media analytics dashboard
  5. You can change the search queries:
    a. Edit the default search terms:excel powerpivot twitter social media analytics dashboard
    b. Refresh Data:excel powerpivot refresh data
    c. Updated Dashboard!excel powerpivot twitter social media analytics dashboard

That’s about it. And here’s a Youtube Video showing some features in this sample:

Conclusion:
In this blog-post, I shared a great sample dashboard built on top of PowerPivot model.

 

PowerPivot: Quick Tip regarding Copy-Pasting Tables in PowerPivot 2010

Standard

Power Pivot Paras Doshi Microsoft Business Intelligence Excel 2010I was working on couple of PowerPivot models recently and both models needed a Date Table. So here’s what I did:

1. Imported the filtered version of the Date Table (DateStream) from Azure Data Market. Added Calculated Columns to the table. Let me call this Table1

2. I copied entire Table1 and pasted it to an another PowerPivot model – let’s call that Table2

 

After this:

And I went to happily create data models! Later, I while testing the model (that had Table2), I realized that it had lost the “formulas” – in other words when I copy-pasted from Table1 to Table2 in PowerPivot – it did not copy-paste the formulas but it just pasted the values.

So, 1. Copy Pasting a Table in a PowerPivot does not copy-paste’s the Formula’s in the columns.

And also it does not keep the “Data connection”. In this case, Table1 was connected to Azure Data source and I could “refresh” data if I chose to. But Table2 was “Static” – In other words, It lost the data connection with the data sources. So I could not “refresh/update” data from the data source if need be.

S0, 2. If you copy Paste a Table in a PowerPivot, the newly copied table does not keep the “Data connection” to data source.

 

I have also posted this here: http://beyondrelational.com/modules/1/justlearned/tips/17976/power-pivot-copy-paste-a-table-in-a-powerpivot-does-not-copy-pastes-the-formulas.aspx