If you hide rows, columns and data on excel, the chart that’s uses this data also hides it — while this is the default behavior, you can override this by following the steps below.
Let’s reproduce the behavior first.
I have a simple excel chart like shown below:
Now, if I hide the data that is selected for this chart then the chart stops showing this as well:
To fix this and if you want the cells (rows, columns and data) to be still hidden but still have the chart show up, then follow the steps:
Select the chart
Under Chart Tools > Design > Select Data
Click on Hidden and Empty Cells
Check the Show data in hidden rows and columns check-box
Go back to excel and you should see the data on the chart now even though the data is hidden
What-if Analysis is a pretty common analysis done by decision makers. Often, they would just create simple excel tables and adjust their variables manually until they get an answer that works. But instead of doing it manually there are features available in excel that will make your life much easier and analysis much more accurate. So, the goal of this blog post is to introduce you to the Goal Seek and Solver feature to help you do what-if analysis in Excel.
#1. Goal Seek:
Let’s say you are a CEO of an e-commerce startup and wondering about what factors you need to focus on to increase revenue. Here’s what the data (*assume per month) looks like when you start out:
And you want to increase the Revenue to $150K from $125K. The three levers you can pull are website visitors, conversion and revenue per customer.
Now you could manually tweak the values for this variables till you get to $150K but as I promised earlier, there’s a better way!
Let’s start with Goal Seek.
You need to set two variables for Goal Seek.
a. Your goal — which in this case is 150K
b. The variable that needs to be changed to achieve that goal — note that you can specify just one variable to do so. So you need to choose out of the three above what you would like to focus on. Let’s say you want to focus on conversion rate.
So once you have these two things — from the Data Tab in Excel, Go To What-if Analysis, Goal Seek:
Now, specify the values. For this example, we want to figure out what should be the new conversion rate so that our revenue will be $150K. So here’s an example of how that would look on Goal-seek:
After entering the values, you will see the status — you can click “OK” to keep the solution and cancel to go back to what you had:
Perfect! So you need to increase the conversion rate from 1.25% to 1.5% to get to the goal that you had set!
#2: Solver add-in
So, you worked on improving the conversion rate for next month or two and you & your team found out that it’s getting really hard to increase it above 1.35% — And also you found that with the less effort you can move the needle on other variables (website visitors & revenue per customer). Now Goal Seek allows you just set one variable so if you more variables than it doesn’t serve the purpose that well! That is where Solver add-in helps.
Think of Solver as advanced Goal seek where you can set more than one cell that can change. You can also set constraints on what the values could be for all the variables that can change.
Now, for our scenario, the conversion rate is at 1.35% but you want to see the possible changes that you can make for website visitors and revenue per customer to reach $150K.
You also know that you can’t above 1,100,000 Website visitors per month and also need to have less than $11 as revenue per customer.
You will need to enable the Solver add-in in Excel and once you do that you will see that in the Data Tab.
Once you have it, open it and fill up the information needed in the dialog box:
a,. Set objective to Total Revenue with value of 150000
b. By changing cells to: Website Visitors and Revenue per Customer
c. Constraints. Website Visitors <= 1,100,000 and Revenue Per Customer < $11
After that click on Solve.
if it found a solution, it would show you that on Excel and also give you additional options to whether you want to keep the solver solution or restore it to original values:
For our scenario, it suggesting that with website visitors to 1,010,101 and revenue per customer to $11, we should hit our goal.
Click on OK when you’re done.
Conclusion:
In this post, we saw how you can use Goal Seek and Solver add-in using an e-commerce scenario but you these techniques can be applied to wide variety of data analysis problems that can be solved using “what-if” techniques.
Hope this was helpful and I would love to hear from you about how will you use this in your work? Or if you use it already then what do you use it for?
Spark-line is a very handy data visualization technique! It’s great when you are space constrained to show trends among multiple data points.
Here’s an example:
But there’s an issue with above chart! Axis values for these group of spark-lines do not seem match – it could throw someone off if they didn’t pay close attention. So a good practice – when you know users are going to compare segments based on the spark-lines – is to assign them same axis values so it’s easier to compare. Here’s the modified version:
And…here are the steps:
1. Make sure that spark-lines are grouped.
Select the spark-lines > go to toolbar > Sparkline Tools > Design > Group
2. On the “group” section, you’ll also find the “Axis” option – select that and make sure that “same for all axis” is selected for Vertical axis minimum and maximum values:
That’s about it. Just a quick formatting option that makes your spark-lines much more effective!
Power Query is amazing! It takes the data analysis capabilities of Excel to whole new level! In this post, I am going to share three reasons:
1. it enables repeatable mash-up of data!
Have you every had to do your data analysis tasks repeatedly on the data with same structure? Do you get “new” data every other week and need to go through the same data transformation workflow to get to the data that you need?
What’s the solution? Well, you can look at MACRO’s! Or you can request your IT department to create a Business Intelligence platform. However, what if you need to modify your data mashup workflow then these solutions don’t look great, do they now?
Don’t worry! Power Query is here!
It enables repeatable mashup of data like you might have never seen before! You need to try it to believe.
It’s very easy to input new data to Power Query and it enables you to retrieve final output based on new data using a “refresh” feature.
Each data-mashup is recorded as steps which you can go back and edit if you need to.
2. It’s super-flexible!
Any data mashup performed using Power Query is expressed using its formula language called “M”. You can edit the code if you need to and as you can imagine such a platform enables much-needed flexibility for the analyst’s.
3. It has awesome advance features!
Do you want to Merge data? How about Join? Are you tired with VLOOKUP’s! Don’t worry! it’s super easy with Power Query! Here’s a post: Join Excel Tables in Power Query
How about searching for online & open data sets? Done!
How about connecting to data sources that “Data” section of Excel doesn’t support yet? (Example: Facebook) – DONE! Power Query makes that happen for you.
And That’s not a complete list!
Plus you can unlock the “Power” (pun intended) of Power Query by using it with other tools in Power BI Stack. (Power Pivot, Power View, etc…) OR you can use the your final output from Power Query with other tools too! After all it’s an excel file.
Action-Item!
If you haven’t already then check out Power Query! it’s free and works with Excel 2010 and above.
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
Analyzing customer complaints in crucial for customer service & sales teams. It helps them increase customer loyalty and fix quality issues. To that end, here’s a mockup:
Note: Drill down reports are not shown, details are hidden to maintain confidentiality and numbers are made up.
In this post, I’ll list few examples from various industries to help you differentiate between business intelligence and data science problems.
Sometime back, I blogged about “Business Analytics Continuum” and in the post we saw that Every Organization has DATA but they use their business data at different levels because of their maturity level. Excel (or other transactional reporting tools) is usually the starting point for any organization – it helps them see WHAT happened. They advance to the next stage, where they get capabilities to slice and dice their data – To find out WHY – and usually this capability is delivered using Business Intelligence tools & techniques. Once the data culture spreads – Thanks to a successful Business Intelligence project – then they soon start to outgrow their business intelligence capabilities by asking problems that need predictive capabilities. This is advanced analytics and Data Sciencestage. To that end, here are 5 examples to help you differentiate between business intelligence and data science problems:
Business Intelligence.(WHAT & WHY)
Data Science & advanced analytics.
Bike Rentals
How many bikes did we rent in Q3 2014? How does that compare to Q3 2013?
What is the trend of total bike rentals at week level? Can you break it down by geography?
Can you predict bike rentals on an hourly basis?
Credit Risk
How many customers have a credit risk of ‘C’?
Can you rank customers by their payments due amount that have a credit risk ‘C’?
Can you predict the credit risk of the customer during contract negotiations stage?
Customer relationship management
How many account cancellations occurred this year (broken down by month and customer segmentation)?
How does percentage of account cancellations this year compare to that previous year?
Can you predict customer churn?
Flight Delays
What is the trend of % of flight delayed this year?
Can you break down flight delays this year by their reasons?
Can you predict whether a scheduled flight will be delayed by more than 15 minutes?
Customer feedback
What is the customer satisfaction % trend this year?
What is the customer satisfaction % broken down by customer segments and product segments?
Can you classify a customer feedback comment into “positive”, “negative” or “neutral”?
SSAS Actions are powerful! You can open web pages, open sql server reporting services, customize drill through reports among other things using actions. In this post, you will see a common requirement from users to navigate to a corporate intranet site from the cube – and usually it needs to be dynamic.
For example, user is interested in seeing the Order Entry Page hosted on the corporate intranet site by using the Order ID from the SSAS cube.
Here’s how you can set it up:
1. Open SSAS Cube in SQL Server Data Tools:
2. Navigate to Actions tab:
3. Here you’ll see three types of action that you can configure
a. Standard (this have five subtypes including the URL action)
b. Drill Through
c. report action
4. For the purpose of this blog post, let’s focus on standard action:
5. Once you click on the “New Action” it will ask you to configure the action:
a. Name: Enter the desired name here
b. Target Type: In this case, Order ID is an attribute member but you will have to choose appropriate target type for your scenario
c. Target Object: In this case, it’s something like [Order].[Order ID] – in your case, you’ll have to choose an appropriate target object
d. Type: URL in this case (also don’t forget to check books online for what other types can do as well)
e. Action Expression: the format of the Action Expression if it’s driven by a parameter would go something like:
f. Additional Properties: I like to set the Caption to clearly indicate the user that they are opening the “Order Form for Order ID 123999”. You can do that by setting the caption property. The format goes like this:
[code language=”SQL”] "Open Order Entry page for Order ID: "+[Order].[Order ID].currentmember.member_caption [/code]
Also set the caption is MDX to True if you are using above format.
That’s about it, don’t forget to test it (after deploying the cube) using excel or other end-user tool of your choice. In the Pivot Table, use the Order ID attribute in Row/Column labels > Right Click on any attribute member of Order ID attribute > Additional Actions > The caption with dynamic order id should show by here for users to click and navigate to the specified URL:
Need to understand the patterns in Quality test results data across all plants.
Summary:
– The solution involved creating a Business Intelligence system that gathered data from multiple plants. I was involved in mentoring IT team, development and end-user training of a Business Intelligence Dashboard that used SQL server analysis services as it’s data source.
– Dashboard development involved multiple checkpoint meetings with business leaders since this was the first time they had a chance to visualize quality test results data consolidated from multiple plants. Since they were new to data visualization, I used to prepare in advance and create 3-4 relevant visualization templates to kick off meetings.
Mockup:
(it is intended to look generic since I can’t discuss details. Also, drill down capabilities had been added to the dashboard to go down to the lowest granularity if needed)
You have a Fact Sales and Fact Target in your data mart. Fact Sales stores values are product sub category level and fact target stores values at product category level because business sets “sales targets” at a higher (rolled up) level. How do you connect it to a single dimension at different granularity?
Solution:
Here’s the table structure, I just made this up for the demo purpose:
1. Fact Sales
2. Fact Target
3. Dim product sub category
so, you went ahead and tried testing by creating relationship’s to single dimension at different granularity in the cube:
Note how the relationship was specified between Fact Target and Product Sub Category Dimension – it’s joined at a different granularity compared to fact sales. it would be help you from a performance standpoint if the fields that you are using to join the fact and dimension is an int.
So, you browse the cube and here’s what you get:
Note the problem: the target values are being repeated for sub categories but that shouldn’t be happening, right? that’s misleading to business users…ok, to recap what we need to do here: hide target values for subcategories since targets are not set at that granularity. but we do need to show them if the business users pulls in product category.
So here’s a measure group property that comes to the rescue!
Go to Fact Target Measure group’s property > Set IgnoreUnRelatedDimension to False
deploy and browse your cube again, here’s what you will see now:
That’s it! you have successfully joined facts at different granularity to a single dimension.
Now, if I hide the data that is selected for this chart then the chart stops showing this as well:
To fix this and if you want the cells (rows, columns and data) to be still hidden but still have the chart show up, then follow the steps:
