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