4 Replies Latest reply on Aug 29, 2018 4:06 PM by Zach DiSalvo

# Distinct Count of Multiple Dimensions

Hello All -

I am wanting to get the count of the number of tenants in a spreadsheet that I am working with. Unfortunately I am not able to share a packaged workbook as the data is sensitive so I have included a table below with a sample of data in a similar format

 Property Tenants Address A Wendys 1111 5th ave B Taco bell, Starbucks 1112 7th ave C Starbucks 1113 9th ave D Dry Cleaner, Liquor store, Starbucks 1114 12th ave E McDonalds, liquor store 1115 13th ave

What I want to have is a bubble chart that shows the tenant name and the total count of each tenant name. So there would be a bubble and the counts: Wendys (1), Taco bell (1),  Starbucks (3), dry Cleaner (1), Liquor Store(2), and McDonalds (1). The size of the bubble would be based on the count so Starbucks would have the largest bubble followed by liquor store and then all the others. To separate the tenants I did a custom split in the tenant column by "," so it then added 3 new columns and would now look like the table below

 Property Tenants tentants1 tentants2 tentants3 Address A Wendys Wendys null null 1111 5th ave B Taco bell, Starbucks Taco bell Starbucks null 1112 7th ave C Starbucks Starbucks null null 1113 9th ave D Dry Cleaner, Liquor store, Starbucks Dry Cleaner Liquor store Starbucks 1114 12th ave E McDonalds McDonalds null null 1115 13th ave

The problem that I'm having is getting there to only be one bubble per tenant, instead I am getting 3 diffent bubbles per tenant, one for each column. Is there a way to avoid this problem? I'd like to avoid as much data manipulation in excel as possible because this would likely be a spreadsheet that download and re-upload once a month so I would rather use calculated fields in tableua.

Any Ideas??

• ###### 1. Re: Distinct Count of Multiple Dimensions

This could be a good opportunity to use Tableau Prep to try to manipulate the file and make it row-based data with one tenant per line, which is what you really want to do packed bubbles.  If you can import the original file into Excel and do a split on Tenants column to get the three separate columns, save that as CSV.

Connect to that CSV in Tableau Prep and do a pivot on those Tenant fields to get your row-based data. I did something similar today with a column-based data set.  When you create the pivot in Prep you would use only the split tenant fields in the pivot and the other fields for Property and Address would be replicated properly to each row.  The pivot basically says "take each column you designate in the pivot and create a row with that value, and replicate the rest of the data to the row".

 Property Address Tenant A 111 5th Ave Wendys D 1114 12th Ave Dry Cleaner D 1114 12th Ave Liquor Store D 1114 12th Ave Starbucks
1 of 1 people found this helpful
• ###### 2. Re: Distinct Count of Multiple Dimensions

I don't have any experience using Tableau prep. Any other recommendations?

• ###### 3. Re: Distinct Count of Multiple Dimensions

Not good ones off the top of my head.  I was thinking LOD calcs but it was ugly in my head. With Tableau in general row-based data is much easier to work with.  If you can get it in the correct state, it makes life much easier.

I used Prep for the first time today after watching the 4 minute video on Pivots.  Took about half an hour to get it sorted on a 302 column based file that I needed to make row-based.  It looks and feels a lot like Tableau Desktop if you are using Desktop already.  You can save the flow you create so that if you were updating this dashboard regularly you can load the new file, run the same prep and output the same file.  You can even have Prep create a HYPER or TDE file for you that you can connect to directly in Tableau Desktop.

• ###### 4. Re: Distinct Count of Multiple Dimensions

It seemed ugly in my head when I was trying to think it through as well. I'll take a look into Tableau prep to see if I can get it figured out. Thanks for the help!