• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Making a dashboard with dynamic filters - is it possible?

reynro03

New Member
Attached is a file with the 6 columns that are going to be relevant (with roughly 4000 rows removed)

My goal is to create a dynamic dashboard that will have tables and charts set up like this:

Drop Down Menu with list of all Regions (including ALL REGIONS as an option)
Drop Down Menu with list of all Reps (including ALL REPS as an option)
Drop Down Menu with two options: Number of Opportunities and Total Estimated Price

By default, all regions/all reps/number of opps will be selected, showing a pivottable and chart with Regions as the rows, probabilities as the columns, and count of opportunities as the body.

When someone chooses a specific region from the drop down, two things will happen:

1) the REPS drop down will automatically filter to only the reps from that specific region
2) the table/chart will automatically update to as if SALES REP was the rows with a filter by that specific region (so if I choose New England, the rows will now be all new england reps)


Is this a possible thing to create?
 

Attachments

  • reynrodashboard.xlsx
    15 KB · Views: 5
Hi

Is this what you are looking for...

Sort of, almost exactly! I like the slicer functionality but hate the room it takes up. Is it at all possible to get a drop down list that has the same affect?

Also, when Everyone is selected, it has a list of all the Reps for every region. I'd prefer it just had the Totals for every region and hid the individual Reps in that case, and only showed the reps when their region was selected.


I have managed to get a sort of working thing going with Link Picture, but that would have to be manually updated everytime new reps joined and made the chart/table go outside the picture.
 
Hi:
It is possible to use the drop downs as well what do you mean by link picture , is it the camera shots you are talking abt. The "Everyone" is entered as a row item in your raw data hence the REP T is showing up when you select Everyone.
 
Hi

Is this what you are looking for...

Attached is a very very very rough outline of what I want this particular section to look like (no functionality built in). That is what I want the default to look like, but then when I select a region it will pull up those specific reps.
 

Attachments

  • reynrodashboard.rev1.xlsx
    28 KB · Views: 10
Hi:
It is possible to use the drop downs as well what do you mean by link picture , is it the camera shots you are talking abt. The "Everyone" is entered as a row item in your raw data hence the REP T is showing up when you select Everyone.


Yes exactly the camera shots.

And when all of the boxes are selected, it has 7 columns (Region, Sales Rep, 0, 10, 25, 50, 100) I'm looking for it to NOT have the Sales Rep. So I guess it might have to link to a separate table?
 
I had to put a macro to meet your requirement
Please see the attached
 

Attachments

  • reynrodashboard.xlsm
    38.9 KB · Views: 23
Nice! So the macro essentially changes the filter for the Region pivot, and if it is anything but All it chooses that filter for the second table based on the index match you have in N6/M5?

Two questions, mainly because I am not great at the VBA side of things;

1) In AP and AQ you have the list of all Reps and Regions....is there a way to have that list automatically created without duplicates/blanks? So everytime a rep is hired the list will automatically adjust?


2) I've seen this before but I don't know how it would be done...Is there any way to hide the first table when a specific region is selected, and not show the second table if ALL is selected?
 
Hi:

I have created a pivot for the drop down list to make it dynamic, now whenever a new region is getting added you will have to just refresh the pivot the drop down will be automatically be updated. Don’t worry about the list of REPs the control for both the pivots are Region drop down. I am not sure why you want to hide the second table, the logic is that the first table and graph will give you a region wise break up and the second table and graph will give you a further drill down based on the REPs, for me it make perfect sense. Let me know your thoughts. Here is the file with the changes and yeah instead of ALL it is grand total to select all the regions and the REPs since the source data for the combo box is now a pivot.
 

Attachments

  • reynrodashboard.xlsm
    70.8 KB · Views: 47
Hi:

I have created a pivot for the drop down list to make it dynamic, now whenever a new region is getting added you will have to just refresh the pivot the drop down will be automatically be updated. Don’t worry about the list of REPs the control for both the pivots are Region drop down. I am not sure why you want to hide the second table, the logic is that the first table and graph will give you a region wise break up and the second table and graph will give you a further drill down based on the REPs, for me it make perfect sense. Let me know your thoughts. Here is the file with the changes and yeah instead of ALL it is grand total to select all the regions and the REPs since the source data for the combo box is now a pivot.

This is awesome, thank you!!
 
Back
Top