I like using spark lines data viz when it makes sense! It’s a great way to visualize trends in the data without taking too much space. Now, I knew how to add sparklines in Excel but recently, I wanted to use that on Google sheet and I had to figure it out so here are my notes:
1. Google has an inbuilt function called “SPARKLINE” to do this.
2. Sample usage: =SPARKLINE(B2:G2) — by default you can put line chart in your cells.
4. One of the best practices that I advocate when you spark-line to “compare” trends is to make sure that you have the consistent axis definition. So the sample usage for that could like this:
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.
Similarly, you can remove Grand Totals:
Alternatively, you can remove grant totals from 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
2. Select the connection that interested for automatic data refresh > properties
3. 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.
click 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 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
2) Download OLAP Pivot Table Extensions and Install it
In my case, I had 64-bit excel so I’ll download the 64-bit version of OLAP 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:
Now let’s add a calculated measure:
Select the Pivot Table > Right Click > OLAP Pivot Table Extensions
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
You can now see that the calculated measure Difference got added to the Pivot Table!
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.
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:
#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:
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:
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
6. you’ll see a slicer on your sheet now:
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
#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:
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
#2: Sliced by Accessories
c. How to change the layout of the Pivot Tables?
Without formatting and changing the layout, the excel pivot table looked like this:
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
Step 2: Now here, Go to Report Layout > Show in 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.
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“:
Step 5: so now it looks as follows:
To recap, Here’s what we had to do: Change the Layout of the Pivot Table & we also hid the expand/collapse buttons too.
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:
It’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?
There’s one thing common between Excel Macro and a Data warehouse: They help an analyst automate tedious tasks. Macro helps automate some of the common excel tasks. Data Warehouse helps analyst automate their “data cobbling/gathering” process. So recently I worked on a task to extract business logic from an Excel 2010 macro. Here’s what I did:
3) Now once the Microsoft Visual Basic for Applications Dialog box opens up, you should see a macro code. Now do NOT press F5 to run the macro! Instead, go to your first line of code and press F8, this will run the macro one line at a time. Open up your excel sheet that had this macro (in second monitor) and see what happens! [Productivity Tip: You don’t need secondary monitor for this but if you do then it will boost your productivity]
The key as you can imagine is to execute the macro one line at a time & visualize it the second monitor. it gives you a good sense of what’s happening (even if you don’t know how to write macro) and you should be able to understand the macro code or document it for data modeller or BI Dev’s so that they can encapsulate the logic in Data Warehouse/Cubes. Or you might be an excel pro trying to understand someone’s macro, then this trick works there too!
Note:
1) I’ve shown the steps that I took in Excel 2010. I haven’t tested if it works with other versions
2) I am not an Excel Macro Expert so If there is a better way, I’d be happy to learn about it. I just happened to figure this out & it helped me understand the logic hidden in a 1300+ lines of macro code.
That’s about it for this post. your feedback is most welcome!
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
After I Flattening it, It should look this:
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.
That’s about it. once you do that, you should see data in flattened format.
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:
Solution:
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:
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
Step 2: Now here, Go to Report Layout > Show in 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.
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“:
Step 5: so now it looks as follows:
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.
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!
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.
