I LOVE Pivot Tables – they are (in my view) the best aggregation tool available to the data analyst today. However sometimes when you aggregate your data, you find yourself at a point where you can’t see the trees for the forest. If this happens, you really need to start to disaggregate your data into the component parts that are driving the overall result. In my post today I take typical business scenario looking at the change in sales $…
By Dany Hoter
Intro by Avi: I have often been asked, about ways to provide an “input” to the Power Pivot model from Excel. Disconnected Slicers are a popular way to do this. But with Excel and Power Pivot, there is always more than one way to accomplish a task. Dany shows us how, while making histograms easier to use. He uses a filter dropdown, which even works with Excel Online – inside a browser! Here is the end result, read on to learn how and download file.
Take it away Dany…
Creating a histogram in Excel based on Power Pivot is not as easy as it should be.
The method I use is no different from what others have already blogged and wrote about. There is even a solution that calculates the number of bins in a histogram with a formula that is based on the total number of cases.
My take on the problem was to let the user choose in run time what is the interval between each bin as a percentage and to show the number of bins accordingly.
The model contains data about a service that is in its infancy and so the users experience a relatively high number of errors.
The managers responsible for the service posed the following request:
“We want to see a histogram of the sessions showing how many users have experienced no errors in all the sessions they initiated, how many experienced errors in 10% of the sessions, 20% of the sessions … all the way to these poor users who saw nothing but errors in 100% of the sessions (Told you it is in early stage…)
Post By Dany Hoter Intro from Rob: In this post, Dany demonstrates how we can use Power Query to add a numerical index column to our table, and then use that to address the previous row in a calc column. Lots of good stuff here. More generally, this is achievable using the misleadingly-named EARLIER function. EARLIER does not mean “previous row,” but coincidentally, you can use it to do just that, and many other “cross-row reference” things. Check out this…
Cloud Data Like SalesForce Available to PowerPivot as if it Were in a Local Database:
My Long Wish for a “Data Highway” Gets Closer Every Day
(Click for the Webinar Featuring Yours Truly on YouTube)
Flashback 2001: The “Data Highway” Concept
Back at Microsoft in 2001 when I was working on what eventually became Excel 2003, I pitched a vision that I called “Data Highway.” (OK, not an original name considering the Information Superhighway thing coined by Internet inventor Al Gore, but invention is smart and theft is genius, or something like that.)
The idea behind Data Highway was simple: all relevant data made available to the most popular tools (cough cough Excel), in a convenient and refreshable format. No manual gruntwork required to “fetch” data in other words – saving your brain for actual thinking.
- A common internet protocol for exchanging data.
- “Teaching” Excel, Access, and other tools to consume any data source exposed via that protocol.
- A marketplace for data where providers like Dun and Bradstreet could sell data to be piped straight into Excel.
Well the protocol flopped and our VP killed the marketplace idea before it got off the ground. Having good ideas isn’t enough – you can’t be too early, and you also need to execute better than we did.
Fast Forward to Today
Here we are at the end of 2012, and we have all three elements available in different (but robust and real) forms:
“I’m telling you there are monkey-fighting hyperlinks in this Monday-to-Friday pivot!”
(Seriously this is how they cleaned up his line for TV, with “monkey-fighting” and “Monday to Friday”)
***UPDATE: I am no longer working at Pivotstream and do not endorse their services. All links are removed from this article but feel free to look them up if you are interested.
Retailer Competitive Overlap Application – New and Improved Live Demo
Revamped/Simplified “Retailer Competitive Overlap” Application
(Note that the Row Labels Area of the Pivot Contains Hyperlinks!)
Clicking an Item to Get More Detail
The retailer overlap application is one that I’ve covered before, in my post announcing our live PowerPivot demo site, but I’ve recently spent some time improving it based on customer feedback and requests.
Specifically, our retail customers have asked the following: “It’s great that I can see that Retailer X competes with me for our senior citizen customers much more aggressively than we thought, but can I get a list of the actual stores that overlap, with addresses?”
But WHICH Stores? I Want to See the Addresses!
Hyperlinks in a Pivot!?
Let’s zoom in on the row area of the pivot pictured above:
“There are people out there whose jobs force them to be the place where two sources of data meet, and they are the ones who integrate and cross-reference that data to form conclusions… …I think a lot of the world is like that.” -Bill Gates circa 2002 People are always asking me if I know Tyler Durden I mean, I’m often asked if I ever met Bill Gates during my time at Microsoft. I did, once, in 2002, when…
So cool… Chris Webb ALSO posted today about downloading weather data from DataMarket. And any post that starts with the words “I don’t always agree with what everything Rob Collie says” gets an immediate boost in credibility – very wise words indeed Click Image for Chris’s Post Chris takes a different approach and goes through the full online UI rather than sharing out a pre-baked workbook like I did. My approach was intended to make things simpler for you. Let…
“And I feel, so much depends on the weather… So is it raining in your bedroom retail?” Example: 800 Days of Weather in New York City 820 Days of Weather Data from New York City, Pulled From DataMarket (Temps in F, Precipitation in Inches) Come on admit it. It’s very likely that you would have a use for data like this, whether it was from a single location or for a range of locations, as long as the locations(s) were…