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!

 

SQL Server Reporting Services: Why am I not seeing every axis label in a chart?

Standard

Problem:

SSRS chart didn’t show all axis labels. Here’s an example.

Note: it does NOT show all country names:

axis labels sql server reporting services

Solution:

So what do you do if you want to show all axis labels in the report and do not want to skip the axis labels? Here are the steps:

1. Go to the Chart Axis properties

2. Under Label, change the value of Label Interval from Auto to 1

ssrs chart aix label properties

3. Preview your report to see if you see ALL axis labels now:

axis label ssrs issue solved

Conclusion:

The above chart is NOT perfect. There are other things that can be done but the goal of the blog post has been achieved! We have changed the axis label property so that all axis labels now show up on charts.

 

SQL Server Reporting Services: Month Names sorting Issue

Standard

Problem:

I was developing a SQL Server Reporting Services report from data that was coming from Analysis Services cube. And when I created the report having Month Names, it was not sorting it correctly.

It was:

April, August, February, Jan….

But It should have been:

Jan, Feb, Mar…

because I had correct sorting settings in the cube. So what was wrong? Or what can we fix?

Solution:

Turns out, the solution was pretty simple. Here are the steps:

1) Select the chart.

2) Select the Month Name category group

3) Go to Properties

4) Go to Sorting section

And delete the sorting property that sorts the Month Names by A to Z. seems this overrides over sorting properties that’s in the cube.

sorting sql server reporting services month names

5) After deleting the sorting property in SSRS fixs the issue.

Please preview the report to see if the issue has been resolved for you.

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

New Digital Marketing Analytics Report shows social media is not the best source of acquiring customers:

Standard

It’s great to see Insights that data can uncover. I saw a nice insight in a report I read about Analyzing customer acquisition channels for e-commerce sites and in this blog post, I am sharing it with you. So what are the top customer acquisition channels for Commerce sites? The Top channels are Organic Search, Emails & Paid Search.Here’s the report: E-Commerce Customer Acquisition Snapshot

It was not surprising to me to see Organic Search and Emails being among the Top customer acquisition channels but what surprised me was  relatively poor performance of social media in acquiring customers. Here’s the chart showing performance of various online channels for acquiring customers:

ecommerce analytics percentage of customer acquired vs. channel

Data Source: http://blog.custora.com/2013/06/e-commerce-customer-acquisition-snapshot/

Note #1: The post is NOT about devaluing the benefits of social media and it comes to down to understanding the goals of having a social media presence in the first place. While computing the ROI of social media, there are other factors like increased brand awareness, customer loyalty to be considered. But I posted this data because it’s a great way to show how data can uncover insights and sometimes it may surprise you

Note #2: The percentage of customers acquired does not add up to 100% for a year because the data does not include things like direct traffic. The author of the report confirmed it over an email w/ me.

That’s about it for this post. Your comments are very welcome!