Interactive Dashboard w/ slicers

[Brapp2Smokin]

Hi all!

I am looking to build out a dashboard that has interactive slicers. One slicer for Region, another based on Manager name.

Goal: Each manager can go to dashboard select the slicer for their name and all data changes to show them only customers and sales rep which reside under them. Also, they are able to see how many accounts they have sales reps assigned to vs how many are unassigned.
E.G. - EMEA has 2 sales managers (Tim/Steffen), Steffen has 1 account with no assigned sales reps. Tim has two accounts. One has an assigned sales rep. The other does not.

Dashboard should show when Tom selects his name that he has 1 customer out of his two that is unassigned.

Issues:
1) The slicers seem to work interactively. However, the Assigned vs Unassigned Pivot chart does not seem to be correctly configured.


Any help would be greatly appreciated. This is a very small example of data. I will be working with around 1500 customers, 30 managers and close to 500+ sales reps.

Thanks!

[stasinek]

I do not know if I made you well? View this solution using Power Query.

[Brapp2Smokin]

Hi Stasinek,

Thank you for the quick response. I am not as familiar with Power Query as I would like to be.

I have a few questions/concerns.

1) "Sheet 1" would be an export of "Raw Data" from a UI. That data comes with the headers as I defined. Therefore, JR/SR is not a column. Sr, Sales rep names go in column E, and Jr. Sales Rep names go in column F.

2) I do not see a situation in your data set that shows a customer not having any assignments. Example, regional managers want to see how many customers they have total. Out of those customers how many are assigned with either a SR/JR sales rep, and how many are not assigned at all. For the ones that have no assignment it would be ideal to have a table below the pie chart that updates with some information of the customers that have no assignments.

3) Why in both my example and your example is NA region being shown in two separate columns? There are a total of 3 NA customers in your data set and 4 in mine, but neither chart shows only one bar for NA. As it does for the other regions.



Thanks!

[Jim Clayton]

Hi. I'm currently working on a project with slicers that might help. Sending you a link to the most recent version that I had posted up here. Maybe you can work with it. On my phone right now so that's about the best I can do. https://www.excelforum.com/excel-cha...than-once.html

[Brapp2Smokin]

Hi Jim,

Thanks for the info!

I had a look at the article, but I am not sure this is what I am needing at this point.

I am not coding anything as of yet. I wanted to keep it simple for the time being as my VB/A knowledge is limited.

[Brapp2Smokin]

Is there possibly a formula that can be written to compare?

I figured this could somehow be done with a basic pivot table, but it is proving to be more difficult.

[Jim Clayton]

Looking at this again. I'm not seeing how you're defining "Assigned" from "Unassigned". I might be wrong, but any chance that's throwing things off?

[Brapp2Smokin]

Hi Jim,

Assigned means there is a sales rep on the account. Whether it be Sr. or Jr. Sales rep. Unassigned means a major has a customer that has no one looking into the account. So no one is assigned to the account.

If the manager sees he/she has 20 accounts. Then sees graph that says 17 accounts assigned/3 unassigned accounts. This will prompt the manager to investigate and get these accounts an assigned sales rep asap.



Hopefully that helps.

[JeteMc]

Perhaps this will help.
The extra NA is due to an extra space after NA in cell C8 on sheet 1.
Change the formulas in E2:F2 on the Assigned vs Unassigned sheet to (respectively):

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Add headers for those cells in E1:F1 and select those for the category axis labels.
Let us know if you have any questions.