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”
3. 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”
4. On the next dialog box, you can select the measure, rule & the filter value:
After 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:
3. select your NEW destination and click on OK.
In this post, we saw how to filter measure values & how to move Pivot Tables.
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.
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)
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:
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!
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:
(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:
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
2) Percentage of passwords having at least one special character vs passwords having no special character:
3) Percentage of passwords that have: at-least one number, one alphabet & one special character AND length = 8 or more.
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%)
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.
2 million passwords in a .txt file.
Information I appended to the data-set using TSQL:
1. Length of password
2. Has Alphabets?
3. Has Numbers?
4. Has special Characters?
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!
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
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.
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!
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!”
You can Skip creating a NEW sheet – but make sure to select appropriate columns for PIVOT TABLE later.
Make sure you are on our NEW sheet.
Go to Toolbar > Insert > PivotTable > Select Pivot Table
Step 7: No change, Just click on OK.
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
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”
click on OK.
Do you see this:
Now Look at your Pivot Table:
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:
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!
Facebook Page Insights Guide. From your Insights Dashboard: