How to change the layout of the Excel Pivot Tables?

Standard

Pivot Tables are powerful mechanism to analyze data. And as you may know, it’s really popular among business professionals that use excel for their day-to-day data analysis needs. With that context, Here’s the blog post on Pivot Tables Layout:

Situation:

I got a question from my client who had a specific need on how she wanted her Pivot Tables to look. Here’s how the Pivot Table should look like:

excel pivot table power pivotSolution:

I am using Excel 2010 Pro Plus to demo the solution, please use this solution to adapt to use it with other versions of excel.

Without formatting and changing the layout, the excel pivot table looked like this:

step 1 excel pivot table from power pivot

Now, let’s work step by step to meet the requirement:

Step 1: click somewhere on the Pivot Table and from the Toolbar, Switch to the Design Tab under PivotTable Tools

Pivot Table Tools Design ExcelStep 2: Now here, Go to Report Layout > Show in Tabular Form

Pivot Table Layout Tabular Form

Step 3: As you might have noticed the “hierarchical” structure is now broken up into multiple structure getting us closer to meet the requirement.

half way pivot table layout

Step 4: In our requirement, you can see that it does not give the user to see the “+” or “-” (expand, collapse) buttons. So, let’s hide these buttons from the report from Step #3

Right click somewhere on the pivot table > PivotTable Options > Display tab > uncheck the box that says “Show expand/collapse buttons“:

pivot table hide expand collapse button

Step 5: so now it looks as follows:

excel pivot table power pivot

So as you can see, it meets our requirement now.

To recap, Here’s what we had to do:

#1: Change the Layout of the Pivot Table

#2: We saw how to hide the expand/collapse buttons too.

 

That’s about it for the post! Thanks for reading.

your comments are most welcome!

 

Data Explorer is now “Power Query” AND yes, you can use with Excel 2010 Pro Plus!

Standard

Microsoft announced a cloud based business intelligence platform called Power BI – as a part of that, the project (in public preview) that was previously called “Data Explorer” will be released as “Power Query”. It’s a great tool that have used to find, clean and shape data in Excel 2010, very useful! So one of the first things I checked was whether Excel 2010 can run Power Query or not. Turns out, it does! It works with Excel 2010 professional plus (Please read the system requirements on the official download page for details)

power query excel 2010 professional

And of course, I downloaded and installed it on my Excel 2010 professional plus.If you’ve not installed Office 2010 SP1 or higher, do that too.

Please note that this change affects some of the blog posts that I’ve published on this blog, Here’s the list:

1) Exploring, filtering and shaping web-based public data using Data Explorer Excel add-in
2) Web Scraping Tables using Excel add-in Data Explorer preview
3) Unpivoting data using the data explorer preview for Excel 2010/2013
4) Merging/Joining datasets in Excel using Data Explorer add-in
5) Remove Duplicates in Excel Tables using Data Explorer Add-in

That’s about it for this post. Update your “Data Explorer” tab to “Power Query” if you haven’t already! It’s a handy tool and I am glad to see that Data Explorer Power Query runs on Excel 2010 Pro Plus!

Custom Calculations for Invoice & Returns using PowerPivot DAX formula

Standard

This is from the thread in the SQL Server PowerPivot for Excel forum.

Problem Description

Here’s the sample data:

STATUS WEEK SALES CUSTOMER
INVOICE W01 $150.00   A
RETURN W02 $120.00   B
INVOICE W02 $120.00   B
INVOICE W02 $130.00   C
INVOICE W02 $150.00   D
INVOICE W03 $130.00   E
INVOICE W03 $120.00   F
RETURN W01 $150.00   A
INVOICE W04 $100.00   G
INVOICE W05 $150.00   H
RETURN W03 $130.00   E
RETURN W02 $120.00   B
RETURN W06 $100.00   I
INVOICE W06 $100.00   I
RETURN W05 $150.00   H

What the user wanted was an output like this:
 Excel Invoices and returns calculations

Without PowerPivo this is how the user was doing it:
“Create one pivot table filtered by INVOICE (WEEK in Columns, CUSTOMER in Rows) and second table filtered by RETURN (WEEK in Columns, CUSTOMER in Rows). Then manually calculate INVOICED pivot – RETURN pivot.”

Solution

Let’s see how DAX formula in PowerPivot can help the user so that it eliminates the “manual” calculation.

So Here are the steps:

Step 1

Create two calculated measures:

Invoiced:=CALCULATE(SUM([SALES]),TABLENAME[STATUS]=”INVOICE”)

Returned:=CALCULATE(sum(DATA[SALES]),TABLENAME[STATUS]=”RETURN”)

 

Step 2

Create one more calculated measure:

Invoiced-Returned:=[Invoiced]-[Returned]
Now from the usability standpoint, Hide measures created in step 1

Here’s the screenshot of the PowerPivot Model:
 Power Pivot Excel DAX Invoice

Step 3

Let’s view this using PivotTables:
 Excel Power Pivot excel and DAX

Conclusion

In this post, we saw how to create custom calculation to handle invoices and returns using PowerPivot DAX formula’s.

Paras Doshi

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

Web Scraping Tables using Excel add-in Data Explorer preview:

Standard

In this blog post, we’ll see how you can do some web scraping of HTML data tables that you see on the inter-webs!

Before we begin, If you haven’t downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx 

First, let’s try doing copy-pasting of tables found on websites into excel without data explorer add-in.

So I found some very interesting tables here: http://powerpivot-info.com/post/16-powerpivot-dax-function-list-with-samples

And here’s my copy-pasting efforts:

excel copy pasting html tablesNow, that requires formatting! I don’t want to do that especially if I am doing that for few more tables – let’s see an elegant way of going about web scraping tables using Data Explorer add-in:

Step 1:

Keep the URL handy.

Now, Open Excel 2010/2013 > switch to Data Explorer tab > click on From Web

Step 2:

Paste the URL that has the tables you need:

excel web scraping html data tables

Step 3:

The dialog box would list all the tables from that HTML page and so you’ll need to select the table that you want.

(optional) if your tables have headers as first rows. Make sure to mark them as headers: Right Click a Column > use First Row as headers

excel data explorer query editorStep 4:

Click DONE and your excel sheet will populate itself w/ the data from the table.

excel data copied from website data explorerThat’s about it for the steps!

Notes:

1) Data Explorer add-in will let you “explore” external open datasets that’s out there on the internet.

2) Please make sure that you’re not violating any copyrights before you go about web scraping and sharing your work.

And 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

That’s about it for this post, 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?

How conditionally formatting your data in Excel can help you save time in answering business questions?

Standard

Visual analytics is amazing – it helps “data enthusiasts” save time in answering questions using Data. Let’s see one such example. For the purpose of the blog post, I am going to show how to do it in Excel 2010:

Problem:

Here’s the Business Question: What was sales of Tea in North Region in 2012 Q1

Here’s the data:

SALES DATA(2012 Q1)  East West Central North South
Coffee  $  7,348.00  $  7,238.00  $  1,543.00  $  9,837.00  $    1,823.00
Tea  $  9,572.00  $  8,235.00  $  3,057.00  $  8,934.00  $  13,814.00
Herbal Tea  $  5,782.00  $  8,941.00  $  9,235.00  $     392.00  $    1,268.00
Espresso  $  9,012.00  $  2,590.00  $  4,289.00  $  7,848.00  $       340.00

So it’s easy to give out answer using the data: $8934

But let me CHANGE the business question:

WHICH Products in WHAT regions are doing the best?

Now this questions is not as easy as the previous one? WHY? because you’ll have to manually go through each number in a linear fashion to answer the question. Now imagine a bigger data-set. It’ll take even more time.

Solution

What can Excel Power users and Data Enthusiasts do to answer the new business question in an efficient way? Well, let’s see what conditional formatting can do it:

Excel Visual Analytics Conditional formatting

Now with the Data Bars, it’s easier to just glance at the report and see best performing products and regions. For instance, it’s very easy to spot that Tea is performing best in South among all products and region.

So how do you create data bars?

1. Select the data

2. Home > Conditional Formatting > Data Bars

Excel Visual Analytics Conditional formatting 2

3.Done! you’ll see this:

Excel Visual Analytics Conditional formatting

4. You can play with other options here to see what suits the best for your needs. But I just wanted to point out that there is a way for you to highlight the data in a way that helps you save time in answering business questions using data

Conclusion:

Visual analytics is a great way to quickly analyze data. In most cases, Human brain is much faster at interpreting the visual results as oppose to text/numbers – so why not use it to your advantage. And tools like Excel have inbuilt functionality to help you do that!