Pivot table

Questions Power Users Ask about Excel: #4 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 #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.

Power Pivot: Casting DateTime to Date in SQL Server source query

Standard

DateTime columns can be tricky for analysis purposes. They don’t work well with Pivot Tables because of the time part, each value seems unique to the Pivot Table & it also creates problems while creating relationships with Date Dimensions. And so, It’s a common need to convert them to just Date before analyzing data & also a common need to create a relationship between the Date (and not DateTime) with Date Dimension Table.

So if it’s possible, I try to do the data type conversion in the source system query. If your source system is SQL Server, you could use this piece of code:

[code language=”sql”]
select [your-fields],cast([DateTime_Col] as date) as Date_Col from TableName
[/code]

Doing the data type conversion upfront in the source system query is a good thing to do. And I hope this is helpful.

Related Posts:
Strange date relationships with #PowerPivot
Date and Date/Time – Sneaky Data Types!

 

How to Flatten Data in Excel Pivot Table?

Standard

How to flatten data in Excel Pivot Table?

By default, its hierarchical but I want the data in a Tabular/Flattened format. How do I do that? I am going to show how to do that with Excel 2010.

Default View (Note the Hierarchical view of Pivot Tables)

Before Pivot Table in Compact Form

BEFORE

After I Flattening it, It should look this:

After Pivot Table Tabular Form Flattened

AFTER

Note: it also depends on data, not every data can be represented in the flattened view. In above example, we’re analyzing each product (evident by product id) and so it supports the flattened view.

here are the steps:

1. Select Pivot Table

2. From the Toolbar, Turn off Subtotals. Go to Pivot Table Options > Design > Sub Totals > Do not show Subtotals

3. Go to Pivot Table options > Design > Report Layout > Show in Tabular Form.

Show in Tabular Form Pivot Table

That’s about it. once you do that, you should see data in flattened format.

How to add calculated measures to Pivot Tables using OLAP Pivot Table Extensions add-in?

Standard

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 OLAPNow 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 memberYou can now see that the calculated measure Difference got added to the Pivot Table!

Excel olap pivot table with calculated memberNote

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.

That’s about it for this post! Your feedback is 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!

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:

DATEAMOUNT
6/1/201350
6/2/201340
6/3/201330
6/4/201320
6/5/201310
6/6/201310
6/7/201320
6/8/201330
6/9/201340
6/10/201350

(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

Getting Started: Implementing Dynamic Security with row filters in Tabular Models

Standard

In this blog post. I’ll help you get started w/ implementing dynamic security with row filters in Tabular Models.

Scenario:

We’ve users that connect to a Tabular Model via Excel for Data Analysis purposes. One of the analysis that they do is Countries VS. Total Margin:

tabular models countries total margin profit

What we want to do is restrict someone from Europe to see data only about France, Germany and United Kingdom

Solution:

1) Open Tabular Model in SSDT (SQL Server Data Tools)

2) Toolbar > Model > Roles

tabular models BISM roles

3)  Role Manager > NEW > change Name to Europe and Permissions to Read

4) Under the Row Filters, for the Geography Table, enter the following code:

=[Country Region Name]=”France” ||  [Country Region Name]=”Germany” || [Country Region Name]=”United Kingdom”

How to edit code for your scenario? change the [country region name] to your column(s) and also change the values

role tabular dax filter ssdt code

5) Click OK

6) Now let’s test it!

7) Toolbar > Model > Analyze in Excel

8) Select the role Europe

dynamic row filter in tabular models9) Click ok.

10) From Pivot Table, Select Margin & Countries:

DAX tabular models dynamic row filters based on location

11) As you can see, since the role Europe was selected for testing purpose in step 8 –  ONLY France, Germany and UK data is shown in our test! This means that our row filters are working perfectly!

I hope this tutorial helps you get started on implementing dynamic security in Tabular models.

Resource:

WhitePaper: Securing the Tabular BI Semantic Model

 

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?

 

Visualizing dataset of 2 million+ passwords:

Standard

I found a data-set of password(s) on DataScienceCentral: Password and hijacked email dataset for you to test your data science skills – And for fun, I played with the data-set for an hour or so:

1) Password Length vs Frequency

1 how to choose password password length

2) Percentage of passwords having at least one special character vs passwords having no special character:

2 passwords that have special character vs the one's that dont

3) Percentage of passwords that have: at-least one number, one alphabet & one special character AND length = 8 or more.

Answer: 1.4856%

Let’s see a comparison of Passwords of length 8 or more (69.302%) vs Passwords of length 8 or more having combination of alphabets & numbers & special characters (1.485%)

4 passwords having combination of alphabets plus numbers and special characters

That’s about it for now – it was fun!

 

And for those interested, here are the few behind the scene technical details:

Tools I used:

1. Excel & 2. SQL Server

Note: I first tried using Google refine to augment data – but it crashed on me. So thought of using SQL Server and TSQL. And if excel 2010 supported 2+ million then I would not have needed SQL server. Anyhow – the tool used is not important here.

Initial state:

2 million passwords in a .txt file.

Information I appended to the data-set using TSQL:

1. Length of password

2. Has Alphabets?

[a-zA-Z]

3. Has Numbers?

[0-9]

4. Has special Characters?

[^a-zA-Z0-9]

Plus few others derived from #2, #3 & #4 like ” has alphabets+ characters + special characters?”

That’s about it for the technical details. Ping me if interested!

 

Related articles:

Social Media Analytics. Facebook Page Smackdown: Status updates vs Images?

Standard

In this post, we’ll answer the question:

For a Facebook page – which post-type* is better at getting more Likes + Shares + comments

*Currently on Facebook page – you can have following Post-Type:

1. Images

2.Status Updates (Post)

3. Videos

4. Link

5. Question

6. Shared Content (i.e. when you “share” content from other Facebook page/user-account)

Now – If you have not seen “Facebook Insight”s for your Facebook page – I’ll encourage you to do that (assuming you have suitable admin role):

view facebook page insights social media analyticsYou’ll see something like this:

facebook page insights dashboard social media analytics

Off-Topic: I like how this report design, I really do: Five things I learned about “Art of report designing” from Facebook insights.

Now, Be a Kid – explore this Dashboard – see what else you’ve got: Likes Metrics, Reach Metrics, Talking about this Metric. There’s interesting Demographic information on each of the mentioned metrics. For instance I know that 60% of my fans are from India. Any-who this post is not about delving into what is available but it is about something that is not available via this Dashboard.

So Now I had this Question: What kind of post should I post more to get more likes/shares/comments? Should I post Images? Should I share Interesting Links? Thus, I wanted to know: For a Facebook page – which post-type is better at getting more Likes + Shares + comments

So as a Data Guy – since they didn’t provide that insight – I thought of a way to get it. Here’s how you can do that too in a step by step fashion:

Tool Used: Excel 2010

Step 1.

On your Facebook Page Insights Dashboard – Do you see Export Data button? yes? Go ahead click it:

– Select a. Post Level Date b. Select Start Time & End Time.

social media analytics download facebook page insights data

Step 2. 

Open the file in excel.

If you do not have access to file that you can use mine – after all the things I share on https://www.facebook.com/Paras.Doshi.Blog is Public! I don’t mind sharing metrics with you. Download the file: Paras Doshi Blog Facebook Page Post Level Metrics Jan1 Aug5 2012

Step 3:

There’s lot of data, right!? Remember: If you think about it, You can extract other actionable insights too and build cool reports.

Any-who to our questions here is what we need:

a. Data in “Key Metrics” sheet

b. Column C: “Type”

c. Column N: “Lifetime Talking About This (Post)”

*People Talking about this include

– Liked, commented or shared your post

– answering a question

– responding to an event.

FYI: if you looking to see number of likes, shares & comments a post gets – check out “Lifetime Talking about this..” excel sheet.
Step 4:

Create a COPY of this Excel File (For Backup purpose – Just in case we manage to mess up the data in this file)

AND then, Delete ROW 2 in Excel sheet: “key Metrics”. Just do it!

delete a row in excel

Step 5:

Now that we have identified the metrics i.e “TYPE” and “Lifetime Talking About This (Post)” – let’s select ONLY this data and create a new sheet. As you can see I have a new sheet titled “Only Important data goes here!”

social media analytics new sheet in excel

You can Skip creating a NEW sheet – but make sure to select appropriate columns for PIVOT TABLE later.

Step 6

Make sure you are on our NEW sheet.

Go to Toolbar > Insert > PivotTable > Select Pivot Table

Social media analytics using excel pivot table

Step 7: No change, Just click on OK.

configure pivot table in excel 2010

Step 8:

Now comes the part where we configure Pivot Table.

– Select All three fields from the “Choose Fields to add to report”

– Drag “Count of Post ID” and “Count of LifeTime talking about this” from Row Labels -> Values

social media analytics facebook page configure pivot table

Step 9:

We do not want the TOTAL number of “People Talking about it” – BUT we want On an Average – for a single post – How many people are “talking about this”.

So let’s change the function of “Life Time Talking about this” to AVERAGE.

Just (left) click on “Count of Lifetime Talking about this (Post)” > and select “Value Field Settings”

pivot table change the function from SUM to Average

click on OK.

Step 10

Do you see this:

pivot table change the function from SUM to Average changed

Now Look at your Pivot Table:

Social Media analytcis for a facebook page

Can you see who won.

In the smack down of Status Updates vs Photo. Photos Won!*

*For my page: That may not be true for all pages! Just saying 🙂

And let me clean up the report a bit:

social media analytics custom dashbaord in excel for facebook page

That’s about it!

Remember you can do MUCH MORE with the Data that you just DOWNLOADED! If you play with this data, please share what actionable insights you were able to extract out the data!

Have fun with Data!

Resource:

Facebook Page Insights Guide. From your Insights Dashboard:

facebook page insights guide pdf