Webi tricks : Selecting Top N customers by Sales Percentage using a Slider

Recently stumbled upon a discussion post which describes a very nice use case for customer interaction using a slider type of input control.

So basically the slider will have a range of 0-100 % and based on the user slider selection the report the report will only show up the result for the top customers contributing the selected percentage of revenue..

So for example if I select the slide to 100% it will show all the customer and if the slider is selected to 0% it will not show any customer and if the user select 70% the report will show up the top customers who contribute for the 70% of the revenue.

Here are some screenshot to show what I mean …

This shows 1.00 which mean 100% and report shows all the stores for it

Now If I select .75 (75%) the below screen shows only the top stores that cumulatively contribute 75% of the revenue shows up in the screen below..

Now Here are the steps to achieve this…

I have create a regular dimension type variable called a  “slider value selection” to store the value selected by the slider. It just a plan dimension with any number as out put..

Now I am creating the slider on top of that dimension with a filter operator as equal to. I am using the range of slider as 0 to 1 with a interval of .01 and default of 1 to show all stores by default. So this will store the user selected slider value to the already created dimension..

Now the next step is setting up the cumulative measure for Sales Revenue which is a very simple variable called “Running Sum Rev” = RunningSum([Sales revenue])

and another variable for total revenue called “total Rev” =Sum([Sales revenue]) In Report

and on top of that I am creating another variable for cumulative percentage as “percentage” =[Running sum rev]/NoFilter( [total Rev])

Please note we want the total revenue to neglect any filter we might apply on the table so I am using a Nofilter() function..

created a Measure:Sales rank

=Rank([Sales revenue];Top)

created another measure : Sales rank threshhold

  =ToNumber(  Max([Sales rank] Where ([Percentage] <= ToNumber( [Slider Value selection])) ) In Block  ; “0” )

created another measure : Slide Filter on Rank

= If NoFilter(  [Sales rank] ) < NoFilter(  [Sales rank threshhold] ) Then 1 Else 0

Now created a block level filter

Slide Filter on Rank = 1

Once this is done… you are good to go …you now have a webi report with advanced user interaction ability which was traditionally only a possible with Dashboard still with much more efforts

Hope you enjoyed this blog… Please share your thoughts and comments..

Also lastly my heartiest thanks to Alberto Garcia  for coming up with a nice use case to solve