PivotTable

Live tweeted #sqlpass’s Business Analytics VC webinar: 13 Excel Tips!

Standard

I was live tweeting during our monthly PASS Business Analytics VC meeting, Here are the tweets to learn about 13 Excel Tips!

Thanks everyone who attended, I hope it was helpful!

Here are some ways to follow the Virtual Chapter:
Website: http://bavc.sqlpass.org/
Youtube: https://www.youtube.com/channel/UCOiRAA4gBxEeVxwmEZ1qy1w
Twitter: https://twitter.com/passbavc
LinkedIn: https://www.linkedin.com/groups/PASS-Business-Analytics-Virtual-Chapter-6701113

Questions Power Users Ask about Excel: #3 of N

Standard

In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #3 of N:

a. How to remove Grand Totals & Sub Totals?

1. Select the Pivot Table.

2. From the toolbar, go to Pivot Table Tools > Design > Sub Totals > Do not Show Sub Totals.

you can also choose to remove it just for rows or columns. it depends on your requirement on how to layout data.

Pivot Tables Sub Totals How to remove

Similarly, you can remove Grand Totals:

Pivot Tables Turn off Grand TotalsAlternatively, you can remove grant totals from Pivot Table options:

Grant Totals Pivot Table Options

There’s also an alternate method for removing subtotals. Put your mouse over the hierarchy level for which you do not need subtotals. Right click > remove subtotals “field name”. But you’ll have to do that for all hierarchy levels if you need to remove all subtotals.

b. How to configure Automatic Data Refresh?

A common question asked by power user is how to make sure that the excel file is pulling the latest data from the cube? Good news for them is that Excel files that are configured to connect to a data source like SSAS cube can be configured to automatically refresh. Here’s how:

1. From the toolbar, Go to Data > Connections

Excel Data Connections Cube Properties2. Select the connection that interested for automatic data refresh > properties

Excel SSAS Cube Property connection3. From here, you can configure the file to do an automatic data refresh every xyz minute and/or configure the file to refresh data every time you open it.

Excel Data Refresh Cubeclick ok when you’re done and close the workbook connections after you’ve configured the data sources that you needed for automatic data refresh.

Conclusion:

In this post, we saw how to remove grand totals & subtotals and, how to configure the automatic data refresh.

In previous articles we saw:

#1: How to sort data? How to add slicers? How to change Pivot table Layout

#2: How to add calculated measures?

Questions Power Users Ask about Excel: #2 of N

Standard

In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #2 of N:

How to add calculated measures (Excel 2010)?

Situation:

Power users in your organization/company are developing Excel 2010 reports against OLAP Cubes. They want to add their own calculations for analysis.

BUT Excel 2010 does not allow end-users to add their own private MDX calculations.

Solution:

A Free Community Excel add-in helps in this case. It’s called “OLAP Pivot Table Extensions”. Here are the steps to download, Install and use it:

1) Check Excel Version (32-bit/64-Bit)

Open Excel > File > Help> About Microsoft Excel

excel version 32 bit or 64 bit

2) Download OLAP Pivot Table Extensions and Install it

Download URL: http://olappivottableextend.codeplex.com/ > Navigate to Downloads Tab & install the right version based on your local excel version.

In my case, I had 64-bit excel so I’ll download the 64-bit version of OLAP Pivot Table extensions.

Excel Pivot Table Extensions

Close Excel and Install add-in

3) Add simple measure

Open Excel.

Connect to Cube.

Let’s add a simple measure that calculates the difference between two measures.

So I created a simple Pivot Table that looks like:

Excel Simple Pivot Table OLAP

Now let’s add a calculated measure:

Select the Pivot Table > Right Click > OLAP Pivot Table Extensions

OLAP PIVOT TABLE EXCEL EXTENTSIONS

On the next screen, please enter the name Difference. and the simple formula:

[Measures].[Meausre1]-[Measures].[Measure2]

(please replace measure1 & measure2 with the measure names from your cube)

And click on Add to Pivot Table

Simple Formula OLAP excel calculated member

You can now see that the calculated measure Difference got added to the Pivot Table!

 

Excel olap pivot table with calculated member

Note

1) OLAP Pivot Table extensions is not supported by Microsoft. It’s a community software.

2) To maintain the single version of truth (after all that’s why you create cubes/Data-warehouse’s!), it’s recommended that calculated measures that end users want in the cube.

Conclusion

In this post we saw a very common question “How to add calculated columns” asked by Power users about excel while they analyze data from SSAS cube.

Previous Article: http://parasdoshi.com/2013/12/10/questions-power-users-ask-about-excel-1-of-n/

Questions Power Users Ask about Excel: #1 of N

Standard

In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #1 of N:

a. How to Sort Data?

There are two ways to do this & here they are:

#1: Select a cell that has a measure value > Right click > Sort > sort the values in ascending or descending from here:

sorting Excel ad hoc report ssas cube

#2: Alternatively, you can also do the same thing by: Select a value from the filed that needs sorting > go to Home Tab > Sort & Filter > from here you should be able to sort data in ascending for descending order:

sorting data excel analysis services cube

b. How to add slicers?

They might not know what slicers are but I’ve been asked how can I add filters where users can see the filter values before picking the value. I’ve also been asked by a Power user for the capability for adding “global filters” who had couple of pivot tables in a single sheet. They also come in handy if you’re building Excel Dashboards. With that, here are the steps:

1. Based on your requirements, decide if multiple items on a dashboard (excel sheet) need to be affected with one click

2. Now, for the purpose of this blog post, let’s assume that you have two Pivot Tables like shown below:

two excel reports slicer 1

Note: the Two Excel Pivot Tables are created by connecting to same cube. And the slicer comes from the same cube.

3. And what if you need to slice data in both pivot tables by one common slicer? Let’s say we want to slice the data in the two pivot tables that we have by Product Category – here’s what you do (assuming that the tables are related in your data source and slicing makes sense)

4. click on any of the pivot table and you’ll see PivotChart Tools in the toolbar

5. Switch to Analyze Tab & click on insert slicer & Select the field that you want as slicer & click OK

pivot table insert slicers

6. you’ll see a slicer on your sheet now:

slicer on pivot tables

7. Let’s format the report to make it look better:

#1: move the slicer such that it does not overlap any of the pivot tables.

#2: In this case, I want to move the slicer to the top of the sheet and change the slicer to have 5 columns. Here’s how you can do that:

select the slicer > from the toolbar > slicer tools > column

slicer tools option excel

#3 I also changed the slicer style from the slicer tools to match its color palette with that of the Pivot Tables:

8. Note that the slicer is connected to just ONE pivot table. Let’s connect it to both pivot tables

9. From Slicer Tools options > Pivot Table connections > check all pivot tables that you want the slicer to be connected to:

pivot table connections excel

10. Done! Test your slicers, with one click you should be able to see that the data gets sliced in both pivot tables:

#1: Sliced by Bikes

excel pivot analysis data 1

#2: Sliced by Accessories

excel pivot analysis data 2

c. How to change the layout of the Pivot Tables?

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 Excel

Step 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

To recap, Here’s what we had to do: Change the Layout of the Pivot Table & we also hid the expand/collapse buttons too.

Conclusion:

In part #1 of N, we saw:

1) How to sort data?

2) How to add slicers?

3) How to change Pivot Table layout?

 

Power Pivot DAX: Difference between two DATE values

Standard

Requirement:

Take difference between two data values.

Example:

EndDate: 11/20/2013

StartDate: 11/14/2013

DateDifference: 6

Let’s start writing some DAX!

Ok, seems simple, right? Try creating a measure DateDifference:=[EndDate]-[StartDate]

Did that work? NO? Does it return date?

Don’t worry, Here’s the solution. Try creating following DAX Measure:

[code language=”sql”]
DateDifference:=1.0*([EndDate]-[StartDate])
[/code]

That’s about it for this post. Here are some related Posts:
Calculate the difference between two dates in DAX
Q: How can I calculate difference between two dates in DAX (seconds, minutes, hours, days and months)
NETWORKDAYS() Equivalent in PowerPivot?

Quick Note about the Filters in the Power View Report:

Standard

You can put the fields as filters in two different areas:

1. View

2. Chart/Table

An end-user asked me the difference between the them today:

Filter area Power View Chart TableIt’s helpful to have more than two chart/table in a view (think of view as one excel single sheet) to understand the difference. So let’s say you have two charts in a single VIEW:

How do you filter data in both charts?

By putting the field in the VIEW area.

They can be called View-Level Filters

How do you filter data in just one of the chart/table?

By putting the field in the Table/Chart area.

They can be called Visualization-Level filters.

Official Resource:

Filtering, Highlighting, and Slicers in Power View

How to Change Data Source of an Excel 2010 Pivot Table?

Standard

Problem:

There’s an excel 2010 pivot table that’s getting its data from TestServer. How do I change the data source so that the Pivot Table get’s it data from ProdServer?

Note: This is a common scenario among Business Intelligence Developers who might want to switch between different servers (Test/Prod) to for comparing data.

Solution:

1. Establish the connection to a NEW data source via Data Tab in Excel.

2. Select the Pivot Table whose connection needs to be changed.

3. From the Excel Toolbar. Go to Pivot Table Tools > Options > Change Data Source

Pivot Table Change Data Source4. Choose the NEW connection from here. (you can use the connection that you created in step #1)

Choose the new data source Pivot Table5. Click OK and that should have successfully changed the data source for you. Please test the column names/values that might have been affected since you changed the data source.

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

Slicing/Dicing data in multiple Excel Pivot Tables with ONE slicer:

Standard

Background:

This week I created a couple of Excel Dashboard’s for a project that I am working on. As a part of the that, I decided to include slicers for these Excel Dashboards. And the reason I did that was because data in multiple pivot tables needed to be sliced with one click. In this blog post, I’ll show you steps to connect an Excel slicer to multiple pivot tables:

Steps for Excel 2010:

1. Based on your requirements, decide if multiple items on a dashboard (excel sheet) need to be affected with one click

2. Now, for the purpose of this blog post, let’s assume that you have two Pivot Tables like shown below:

two excel reports slicer 1

Note: the Two Excel Pivot Tables are created by connecting to same cube. And the slicer comes from the same cube.

3. And what if you need to slice data in both pivot tables by one common slicer? Let’s say we want to slice the data in the two pivot tables that we have by Product Category – here’s what you do (assuming that the tables are related in your data source and slicing makes sense)

4. click on any of the pivot table and you’ll see PivotChart Tools in the toolbar

pivot table charts tools5. Switch to Analyze Tab & click on insert slicer & Select the field that you want as slicer & click OK

pivot table insert slicers6. you’ll see a slicer on your sheet now:

slicer on pivot tables

7. Let’s format the report to make it look better:

#1: move the slicer such that it does not overlap any of the pivot tables.

#2: In this case, I want to move the slicer to the top of the sheet and change the slicer to have 5 columns. Here’s how you can do that:

select the slicer > from the toolbar > slicer tools > column

slicer tools option excel

#3 I also changed the slicer style from the slicer tools to match its color palette with that of the Pivot Tables:

8. Note that the slicer is connected to just ONE pivot table. Let’s connect it to both pivot tables

9. From Slicer Tools options > Pivot Table connections > check all pivot tables that you want the slicer to be connected to:

pivot table connections excel

10. Done! Test your slicers, with one click you should be able to see that the data gets sliced in both pivot tables:

#1: Sliced by Bikes

excel pivot analysis data 1

#2: Sliced by Accessories

excel pivot analysis data 2That’s about it for this post. Your comments are very welcome!

Related Posts:

Comparing Slicers in Excel 2010 to Standard PivotTable Filters

 

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!

 

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:

STATUSWEEKSALESCUSTOMER
INVOICEW01$150.00  A
RETURNW02$120.00  B
INVOICEW02$120.00  B
INVOICEW02$130.00  C
INVOICEW02$150.00  D
INVOICEW03$130.00  E
INVOICEW03$120.00  F
RETURNW01$150.00  A
INVOICEW04$100.00  G
INVOICEW05$150.00  H
RETURNW03$130.00  E
RETURNW02$120.00  B
RETURNW06$100.00  I
INVOICEW06$100.00  I
RETURNW05$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