Creating a Histogram with a User Defined Number of Buckets

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.

Business Case

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…)


Read the Rest

How to Compare the Current Row to the Previous Row in Power Pivot

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…

Read the Rest

Cloud Data Approaching Critical Mass: Connection Cloud, SalesForce, PowerPivot, & Webinar on YouTube


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.

imageThere were three elements to the pitch:

  1. A common internet protocol for exchanging data. 
  2. “Teaching” Excel, Access, and other tools to consume any data source exposed via that protocol.
  3. 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:

Read the Rest

“Drill Across” in PowerPivot – Live Demo

Hyperlinks in a Pivot

“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

PowerPivot Retailer Competitive Overlap Application With Drill Across

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:


Read the Rest

Datamarket: Quick Followup

  “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…

Read the Rest

Apparently April 12 is “DataMarket Weather Day”

 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…

Read the Rest

Download 10,000 Days of Free Weather Data for Almost Any Location Worldwide

“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…

Read the Rest

DataMarket Revisited: The Truth is Out There

How many discoveries are right under our noses, if only we cross-referenced the right data sets? Convergence of Multiple “Thought Streams” Yeah, I love quoting movies.  And tv shows.  And song lyrics.  But it’s not the quoting that I enjoy – it’s the connection.  Taking something technical, for instance, and spotting an intrinsic similarity in something completely unrelated like a movie – I get a huge kick out of that. That tendency to make connections kinda flows through my whole…

Read the Rest
  • 1
  • 2