Yes, I agree it is pretty detailed, I was trying to give the overall picture of what I am trying to do, but you are right it is better to focus on the one detail at a time.

What I am stuck on is filling the smaller table that shows the count of how many times a continent appears in the larger table after it has been sliced. I have re-attached the screen shot of the tables as it would look after a slice has been done for the bigger table, and how the smaller table would be populated based on the slice that was done.

In the screen shot the smaller table, in the count column, those numbers are manually filled at this time.

In the continent column of the big table it shows Africa appearing twice, Asia four times and so on.  In the right most column, column labled C, I have numbers associated with each continent, Africa being 1, Asia being 2 and so on. I tried using a formula to count how many times a particular number appeared so it would populate the appropriate cell in the smaller table below that.  Each cell labeled count in the smaller table below would have a formula that counts the appropriate number in the column labeled C in the bigger table.

I have also attached Book2 which is the table we are working off of.  The reason I am attaching it is I still haven't figure out why when I manually put the formula in the past problem you solved, it didn't work but when I copied the table into my workbook it did work.

John

See if the example in the attached file does what you need. It's the data table with a Power Pivot that displays a distinct count of Companies by continent and a slicer on continent. Can't you use pivot tables like this one as a basis for your charts?

Riny,

Thank you again but this isn't what I was looking to accomplish.

I've attached Book 3.

The first step I would make is choose an industry and then a service.  Once that is done I want to be able to see the count of each continent that is a result of the two slices done.

For instance, if I choose Fin Svcs and Apmnt Stg, there is 2 Africa, 4 Asia, 3 Europe, 3 North America, 1 Oceania and 2 South America.

My intent is not to slice based on continent, but to count the number of each continent that is in the result of industry and service selected.

Hope that makes sense.

John

John,

I'm confused. In your very first post you mentioned that you wanted to count unique company names. The example in your last post suggests that you are counting the number of rows for each continent, allowing for duplicate company names.

The attached file (again) contains a (power) pivot table to demonstrate the difference between a distinct count and a regular count of company names by continent for the selection made.

Up to you to decide which one is correct. If you are NOT looking for the distinct count you may create a regular pivot table.

Riny

Hi Riny,

I apologize for any confusion that I am causing. My initial post was the first problem I was encountering, once you solved that for me, I wasn't able to move on and figure out the second issue I am facing.

Stepping back for a moment, it is important for me to explain what the customer is looking for when I present the dashboard to them.  The reasons they make decision are based on price, selection of best fit companies (BPOs) and finally geography.  For this explanation let's ignore price.

I have attached two screen shots of the two charts that for this discussion are relevant.

The screen shot "Bothpreslice" shows what is the initial state of the table when nothing has been sliced. The bar chart shows 4 bars, the bottom 2 bars are the total companies (16) and total sites (37) and will not change.  The top 2 bars while the same when slices have not been made yet, will reflect changes once they, the slices are made.  The next chart, shows the total number of sites located in each continent prior to any slices occurring.

The screen shot "Bothpostslice" shows how the available companies and sites in the bar chart and sites per continent in the other chart have changed once the selection in the two slicers have been made. As you can see in the bar chart the top 2 bars have changed.  The number 15 simply represents the number of sites that are a result of the slicing that was done and the number 8 represents the unique number of companies.  From that it is obvious that some companies have more than 1 site that qualifies based on the slices made.  The other chart, shows the number of sites remaining in each continent based on the same slices being made.

Finally I've include a screenshot "PostSliceTable" to show where the results come from for the bar chart and other chart.

My struggle has been to get the correct number of sites for each continent to fill the table to update the chart.

Thanks,

John

Okay! But please upload the screenshots again as they didn't come through.

Here are the three screen shots.

Okay, now I'm with you. No need for DM or Power Pivot. See if you can follow / reproduce what I did in the attached file. You may want to fine-tune it to suit your color scheme.

Riny,

Yes it works very well, thank you.

I have a few more things that I haven't worked on yet, hopefully I'll be able to figure them out.

John

Hi Riny,

I was able to figure out 2 of the 3 remaining items, other than some formatting on the dashboard, but the third item I can't seem to figure out.

I created a pivot table in the CityListTab that shows all the cities in the table and their average hour rate.

In the table tab, I have the bar graph that I want to show in the dashboard, but I want the bar graph to change based on the slices that are done.  Much like the other counts we did, but instead I'd like this to list the actual city names and I'll then have the bar graph showing the average hourly rate.  I included a slicer, manufacturing to show if that is selected there should only be 12 cities.

How can I relate the table results of slicing to the bar chart I want to show in the dashboard.

Thanks,

John

Opened the file, see the bar chart  but no slicers. ???

Ok I have reattached the file , I have one slicer in there, manuracturing.

Well, you can't connect a slicer to both a table AND a pivot table/chart, though you can connect a single slicer to multiple pivot tables provided they share the same pivot cache. Now, you can avoid adding another slicer on the pivot table/chart if you include the Selected field in the filter area of the pt and set it to 1. See attached.

Now set the slicer on the data table and press Refresh All to refresh the pt. The chart will update as well. Can't think of any other way. Probably, the refresh could be driven by VBA based on a slicer change, but VBA is something I don't touch.

Riny,

Thank you, I was afraid that it wouldn't be able to be done seamlessly, without refreshing after each slice.

The workaround I have come up with is to include more slicers in lieu of the pivot chart.  These slicers show what countries and cities remain based on slices made, and they can also be used to further down select. I think I can live with this for now.

I've attached two screen captures to show you what I mean.  The first one is based on no selections made, the second is based on choosing manufacturing.

I like that the right most slicers have slider bars as there will be many cities when I have actual data in there.

The left most slicers are all individual slicers. Is there any way to combine individual slicers into a master slicer where each button represents the individual slicer.  The 5 left most are all industries, and eventually i will have 19 or 20 industries in my table.  It would be great to have one slicer that has 19 or 20 buttons. The next group are services provided which I estimate will be between 16 and 20 separate services.  Ideally I would have two "Master Slicers" one for Industry and one for Services.

Thanks,

John

Not sure I follow your additional requirements, though I feel you need to look into changing the way you set-up the data.

I would unpivot columns J to R and create one single column for the Department. Then you can add one slicer like the ones you have on the right containing all departments.

And to avoid too much duplication I would probably create a data model with a separate table for unique Companies by location and their values for High, Low and Average. And another table for all Departments per Company/Location. So then we are back to Power Pivot again. Haven't really thought this trough, though, and can't spend much time on this due to other commitments.