How to get descriptive statistics in Excel?

Problem:

you are analyzing a dataset and before modeling/analyzing you need to generate descriptive statistics on a field. you have the data loaded in Excel and wondered if there’s a way to do that in Excel.

Solution:

There’s an out of the box solution that will support your needs to generate descriptive statistics on a field. Here are the steps:

Note: for the purpose of this blog post, I am using Excel 2013 but data analysis toolpak is available in Excel 2007+.

1. Active “Data Analysis” toolpak.

Follow this steps:  File > Options > Add-ins > Manage: Excel Addins > “GO”

excel data analysis toolpak

2. make sure to check the “analysis toolpak” checkbox.

3. Now you should see a “data analysis” option under the “Data” pane:

Excel Data Analysis Descriptive Statistics

4. Now click on “Data Analysis” and select one of the following options:

Anova, Correlation, Covariance, Descriptive Statistics, Exponential Smoothing, F-Test Two-Sample for Variances, Fourier Analysis, Histogram, Moving Average, Random Number Generation, Rank and Percentile, Regression, Sampling, t-Test, z-Test.

in this case, let’s go with descriptive statistics but you can see that you can perform other tasks as well.

5. Once you click on the descriptive statistics, a dialog box will show up and you will have to enter some data like your input range to generate descriptive statistics. Once you have filled the data needed, click on OK and it should generate descriptive statistics for you in EXCEL!

I hope that helps!

Conclusion:

In this post, we saw how to generate descriptive statistics in Microsoft Excel.

Author: Paras Doshi

Cost Driver’s Dashboard for a Supply Chain Executive:

Cost Driver’s Dashboard for a Supply Chain Executive:

Summary:

Profitability equals revenue minus costs – To that end, A supply chain executive is mostly focused on optimizing cost elements to drive profitability. Here’s a mock up of a dashboard created for an executive to help him keep an eye on the overall health while making sure he gets alerted for key cost categories.

The Dashboard was created using profitability data-set & also had drill down capabilities to analyze numbers for cost buckets like Raw materials, manufacturing & logistics.

Mockup:

Supply Chain Cost Drivers Profitability Dashboard

Power Pivot: How to get Month Name from a date field?

Problem:

How do you get a Month Name from a date field in Power Pivot?

Solution:

here’s a code snippet that should help:

=FORMAT([date],"MMM")

This should give you month names (Jan, Feb, …) instead of integers that are returned by the MONTH function.

couple of notes:

1. date field needs to be used to get the month name

2. MMM needs to be in uppercase.

I hope this helps.

Back to basics: continuous Vs. Discrete variables and their importance in Data Visualization.

Take a look at the following chart, do you see any issues with it?

month trend chart line chart string to date

Notice that the month values are shown as “distinct” values instead of shown as a “continuous” values and it misleads the person looking at the chart.  Agree? Great! You already know based on your instincts what continuous and discrete values are, it’s just that we will need to label what you already know.

In the example used above, the “Date & Time” shown as a “Sales Date” is a continuous value since you can’t never say the “Exact” time that the event occurred…1/1/2008 22 hours, 15 minutes, 7 seconds, 5 milliseconds…and it goes on…it’s continuous.

But let’s say you wanted to see Number of Units Sold Vs Product Name. now that’s countable, isn’t it? You can say that we sold 150 units of Product X and 250 units of product Y. In this case, Units sold becomes discrete value.

The chart shown above was treating Sales Date as discrete values and hence causing confusion…let’s fix it since now you the difference between continuous and discrete variables:

Statistics Discrete Continuos Variable Data Visualization

Conclusion:

To develop effective data visualizations, it’s important to understand the data types of your data. In this post, you saw the difference between continuous and discrete variables and their importance in data visualization.

How to train your users to create their own Business Intelligence reports? #4 of 5: Sample Training Content

In part #1, I wrote about why is it important to enable business users to create their own BI reports.

In part #2, I wrote about three pre-training preparations – 1. Data 2. Tool 3. Understanding Culture.

In part #3, I wrote about 1. User Experience 2. Trainer 3. Training Content.

In this post, I am going share sample training content that uses Excel 2010. Before I share sample content, here are some tips

1. use YOUR data!

2. Show them the end goal & then walk through the steps to get there

 

Here’s a sample training content for a 4 hour-long excel training session (divided into basics & advanced) including hands on lab time.

Here you go:

GOAL:

Excel analysis services business intelligence dashboardBASIC-I

  1. Open the Template
  2. Explore the Field List
  3. Explain the concept of “dimensions” & “measures”
  4. Create a Simple Pivot Table – Row Labels & Measures
  5. Add column labels & report filters

Excel Pivot Table SSAS Step 1

Basic-II

  1. Sorting
  2. Turning off grant totals
  3. Creating a hierarchy
  4. Changing the Pivot Table Design

Excel analysis services business intelligence dashboard step 2

 

Advanced-I

  1. Remove fields from Pivot Table
  2. Add more than one pivot table
  3. Add slicer
  4. Connect slicer with every pivot table

Excel analysis services business intelligence dashboard step 3

Advanced-II:

  1. Add Pivot Chart
  2. Add one more slicer
  3. Add hierarchy structure to pivot tables
  4. Add conditional formatting
  5. Format chart

Excel analysis services business intelligence dashboard step 4

Conclusion:

In this post, I shared a sample training content that uses Excel 2010.

How to train your users to create their own Business Intelligence reports. #1 of 5: Why do it?

Introduction:

Business Intelligence (BI) helps an organization make faster & smarter decisions – There’s no doubt or debate over that! But every organization needs to go through the process of driving Business Intelligence adoption before they start seeing the Return on Investment for a BI solution. One of the form of BI which has been really successful at being adopted by business community is called “self-service BI” and the idea is to enable business users to create their own reports – in other words, “self-serve” their data needs. In this blog series, I would share the best practices that I’ve picked up while leading up an effort to train 200+ business users with the goal of enabling them to create their own BI reports.

In this post, let’s step back and talk about why is it important to enable business users to create their own reports?

#1: Information Technology’s Time & Budget.

The demand for “data” by business users is a great thing! But IT would need to dedicate resources to make it happen. usually, IT teams can be constrained due to budgetary reasons or due to other higher priority items on their radar. So what happens? Business community do not receive the data that they asked for in a timely fashion. And if you cannot offer data when they need it then it might not be as useful. So what’s the solution? One solution is to have “BI platforms” where data assets are readily available for business users to consume. So once they are build, IT would no longer have to dedicate development resources on them. And since IT is no longer a bottleneck, business users can get the data when they want it and however way they want to see it.

Time is money Information Technology Budget

#2: World changes. Business Changes.

World Changes and so does Business. If Business doesn’t adapt to change then they will die! And if Business changes, the data needs would change too. How many times does IT get “change requests” to their production reports? All of these requests would require IT’s intervention to accommodate change requests and what is the average turn around time in your organization for that? How about publishing data assets that businesses can readily access that let’s them see same data from varying perspectives? Of course, they might ask for data that’s not available yet but then IT can spend some time on bringing those data assets into the BI platforms rather than spending resources on “change requests”.

World Changes Business Changes Data Business Analytics Changes

#3: It’s great for BI adoption!

With the flexibility of Self Service BI solution, the control is in the hands of the users (again!). Without having to wait on IT, they can get to the data instantaneously and so faster and there’s a greater chance that they would use “data” in the decision making process. Isn’t it great?

Also, business users who would have created their own reports would share it with others, won’t they? And that would spark collaboration among business users. This is great because the recipients of the reports would start wanting to analyze the data too. And with time, the trust for the data assets would grow among the business community. This is really important for BI adoption.

Conclusion:

In this post, we saw why is it important to train your business users to create their own BI reports using a Self Service BI Platform. In next posts, we’ll get into strategies & tactics to enable users to create their own reports.

Questions Power Users Ask about Excel: #4 of N

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 #4 of N:

a. How to Filter Measure Values?

Report Filters are a great way to put constraints on the data that is displayed. So once a user gets the concept of selecting dimensions as report filters & slicers, they might start wondering how to filter measure values? How do they show a list of customers that have ordered less than $100,000 worth of products? They might try to drag a measure to the report filter section but that won’t work & Excel will throw an error. So how can users filter measure values? it’s a feature of Pivot Table called “Value Filters” and that would be helpful here. Here are the steps:

1. Select the Pivot Table.

2. click on a drop down menu besides “row labels”

Excel Pivot Table Value Filter3. After that, go to Value Filters. You can see that you can filter by applying different rules like Top 10, less than, greater than, etc. Let me demo “Less than or equal to”

Pivot Table Excel SSAS cube filter values measure4. On the next dialog box, you can select the measure, rule & the filter value:

Pivot Table excel ssas cubeAfter you’ve set them up, click on ok and the Pivot Table will filter by measure values.

Note: Be careful when sharing the files that have value filters with other users, let them know that you’ve value filters set on the report as the value filters are not clearly visible if you’re looking at the file for first time.

b. How to move a Pivot Table?

If a user is trying to create a dashboard using excel, they’ll have more than one Pivot Table in a sheet. In that case, they’ll need to move the Pivot Tables around. here are the steps:

1. select the Pivot Table.

2. In the menu bar, go to Pivot table Tools > Move Pivot Table:

Pivot Table options Excel Move

3. select your NEW destination and click on OK.

Move Excel Pivot Table 2010That’s it!

Conclusion:

In this post, we saw how to filter measure values & how to move Pivot Tables.

In earlier articles we saw:

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

#2: How to add calculated measures?

#3: how to remove grand totals & subtotals and, how to configure the automatic data refresh.

Three Power Pivot Installation FAQ’s:

Q1: How Can I upgrade Power Pivot on my machine?

A1: Uninstall Existing version (Yes, it’s not intuitive, but you have to uninstall existing version). Download the version that you want to upgrade to. Install it.

Q2: I’ve SQL 2008 R2 on my machine. Can I install “Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010″?

A2: Yes, go ahead! “SQL server 2012 sp1″ in the name is confusing :) It doesn’t matter which version of SQL server you’ve on your machine – In fact, Power Pivot does not need a SQL server to be installed on your machine.

Q3: How do I check Power Pivot version?

A3: Excel > Power Pivot > Settings > You’ll see version # on the top part of the screen.

Power Pivot Settings Excel

I hope this helps!

Questions Power Users Ask about Excel: #3 of N

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

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/