Log In
Register
Home
Dashboards
September 7, 2023
Offline
I would like to count the unique company names in a column of a table after I have filtered (used a slicer) so that the result could be in a bar chart.
I have included the worksheet, the result I get is the unique number of companies in the total count of companies, not in the result from the action taken.
I've googled the question in a number of different ways with a few different answers but I always seem to get the same result.
See Answer
Moderators
January 31, 2022
Offline
Not sure I got it right but perhaps the attached workbook contains the solution you need. I added a column that determines if the row is visible (1) or not (0).
Then I extended the COUNTA formula with FILTER.
Dashboards
September 7, 2023
Offline
Riny
Thank you for your quick reply. I couldn't get the formula to work, kept getting error messages.
I uploaded another version of the worksheet, this time I have the complete table in one tab, and the other tab I have the slicers that I am testing.
There are a total of 16 unique companies. For this I will be slicing on any one of the industry slicers, which are on the left, and then any one of the services which are on the right.
For instance, if I choose Retail I ended up with 20 total results with 11 unique companies. If I further select Support, I end up with 16 total results and 8 unique companies.
The reason I am building the dashboard is to help customers choose which companies, and which sites they want to consider for outsourced workers. One of the charts I will have is a bar chart that has one bar showing the total number of companies and another bar showing the total number of sites, this part of the bar chart will be static. The third and fourth bars will show the same amount of companies and sites at the start of the presentation, but then as selections are made they will dynamically change to reflect narrowing down of choices. I will most like have over 100 companies and 100's of sites in my data set. Once the industry and services are selected, the customer will then choose preferred geographies, to further narrow down the selection process. Hopefully the use case helps explain what I am trying to accomplish.
Moderators
January 31, 2022
Offline
Does the attached file work for you? If not, which Excel version are you working with?
Answers Post
Dashboards
September 7, 2023
Offline
Yes it worked!
Thank you so much.
When I used the formula it didn't work, but then I copied your worksheet into mine and it worked then. Not sure why it worked that way and not by just using the same formula in my existing worksheet.
I am on Version 2308 ( Build 16731.20234 Click to Run)
Dashboards
September 7, 2023
Offline
Hi Riny,
I am trying to do two similar actions and I again can not get the formula to work. Yesterday I used your formula and it didn't work, but when I copied the worksheet into my master it did and continues to work.
It makes me wonder if it's the release of Excel I am on?
The first of the two actions I want to do, is count the number of continents by continent that end up in the result from slicing. I created another column named it C, short for Continent. In each row I assigned a number for the continents. Africa is 1, Asia is 2, Europe 3 and so on. Then I created a table with two columns, first column lists the continents, and the second column will have the number of instances of that continent based on the result of slicing. I used the same format as for the unique count but I replaced BPO Name with Continent and Selected with C, and for the corresponding cell in the table the appropriate number, i.e. 3 is for Europe. I will use the table to have a tree chart to show the relative concentration of instances by continent.
=COUNTA(UNIQUE(FILTER([Continent],[C]=3)))
After putting this in the appropriate cell, I get the following error message:
Not trying to type a formula use an apostrophe....
I've also tried it without the unique in the formula.
The other action I am trying is to create a list of the cities that are in the result from slicing, no luck there as well.
I do appreciate your help.
Moderators
January 31, 2022
Offline
If you can use UNIQUE it's not a version issue. The error message "not trying to type a formula ....." makes me believe that you need to use as semi-column to separate the function arguments. That's a local setting.
So try:
=COUNTA(UNIQUE(FILTER([Continent];[C]=3)))
Dashboards
September 7, 2023
Offline
Same error message:
There's a problem with this formula.
Not trying to type a formula?
then it goes on to explain the = and - sign and to get around it use an apostrophe first to get the text
Moderators
January 31, 2022
Offline
Please share a file again. Difficult to diagnose otherwise.
Dashboards
September 7, 2023
Offline
The numbers in the table are just input manually at present.
Moderators
January 31, 2022
Offline
My formula sits in A39 and works as intended. It counts the number of unique company names in the table as displayed. Thus, without filter it counts 16 unique company named. Filter for North America and you'll get 5.
Looking at the table you entered at the bottom, I believe you are going to need the Distinct Count option found in Power Pivot. You can create a Pivot Table / Pivot chart that uses the distinct count (rather than just count) of Company Names, for instance.
Then the formula in A39 serves no purpose as it will only return 1 number based on the rows displayed, you but can't use it to create the kind of table you added at the bottom of the work sheet.
Dashboards
September 7, 2023
Offline
Hi Riny,
I appreciate your response but I am not sure I understand. Am I correct that I can not have both a unique count of companies, and the count of each continents appearance after a slice is done. My intent is to have those results drive two different charts on my dashboard.
Would it be ok with you, if I take a screen shot of my draft version dashboard ( I think my workbook exceeds the upload limitation) and give an explanation of each chart and how I am supplying the data to it. I can also provide a screen shot after slicing to show the changes that occur.
I am traveling today, so if the answer is yes, it may not be until tomorrow before I can upload it.
In either case, thank you.
JD
Moderators
January 31, 2022
Offline
Not sure what you mean by "unique count of companies, and the count of each continents appearance after a slice is done". Good idea to upload a screen shot. Anything to help me understand your needs.
Dashboards
September 7, 2023
Offline
Hi Riny,
I have attached a word document and 7 screen shots, to explain and demonstrate what I am trying to accomplish. I didn't see any other way to do this.
The word document describes the process and at each stage identifies which screen shots are associated with that stage.
Thank you again for your time and efforts.
John
Moderators
January 31, 2022
Offline
Hi John,
Impressive pictures and a very detailed Word doc, but it's too much detail to comprehend. Can't we focus on your initial table? From it you want to make a chart that is responsive to a slicer, counting the number of distinct customers per continent. If you can do that, you can probably do similar analyses in other areas.
Riny
Dashboards
September 7, 2023
Offline
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
Moderators
January 31, 2022
Offline
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?
Dashboards
September 7, 2023
Offline
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
Moderators
January 31, 2022
Offline
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
Dashboards
September 7, 2023
Offline
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
1 Guest(s)
