7 Replies Latest reply on Jun 27, 2018 6:39 AM by erik anderson

# Count X / Employee Count

[Edit: Added a dataset]

I have two tables. One is detailed records (sales) and another is a summarized (employee count). The two are relateable by date (year) and geography. I am attempting to do a count of records per year / employee count per year and have the data roll-up and drill down.

Example:

The expected output would be:

2016 Arizona = 4/100

2016 California = 2/150

2016 Total = 6/250

How can I do this? I am thinking I need a staging table... but this seems like it is exactly the type of scenario that would be easy in Tableau.

YearGeographyEmployeeCount
2016Arizona100
2016California150
2017Arizona120

TransactionGeographyDateInvoiceAmount
123456Arizona1/2/2016100
123457Arizona1/2/2016125
123458California1/2/2016100
123459Arizona1/2/2016100
123460California1/2/201675
123461Arizona1/3/2016

125

• ###### 1. Re: Count X / Employee Count

Hi, Erik

Please find my solution attached.

Below is the steps for your reference.

Hope this helps

ZZ

1 of 2 people found this helpful
• ###### 2. Re: Count X / Employee Count

Thanks.

I am attaching a sample workbook; sorry for not doing this originally. While your method will work for the dimension, it will not work without the dimension. The correct values are displayed from the Excel workbook calculation.

• ###### 3. Re: Count X / Employee Count

Hi, Erik

Thanks for your feedback.

Please find my update attached.

Below is the screenshot of calculation I modified.

Hope this helps

ZZ

2 of 2 people found this helpful
• ###### 4. Re: Count X / Employee Count

Thanks for the added help; what you have done is accurate for the specific scenario. I was hoping for something that could better handle drill-down/drill up. For example, if I use the hard-coded version above and drill down to Quarters, the calculation fails. I was hoping there was a means to look at the relationship to the join when making a calculation. I want end users to not be able to misinterpret what is presented.

Is there a way to do that?

Thanks again for the help

• ###### 5. Re: Count X / Employee Count

Hmm. I think you have pointed me in the right direction enough that I can do this. What I really need to do is mess with the denominator while allowing the numerator to change. This can be accomplished through two calculated fields:

While this is still not dynamically referencing the level of the join, it allows enough re-use to be meaningful. I will need to play with it a little more to see what happens when the state is rolled up to a higher level (e.g. Southeast), but I think this will work. Thanks again.

Example:

• ###### 6. Re: Count X / Employee Count

Hi, Erik

If you need the drill down, you probably need add some more dimensions in the calculation shown below, e.g. to quarter level, you can add month level by appending datetrunc('month',[Date]) after the quarter.

ZZ

• ###### 7. Re: Count X / Employee Count

The correct answer to this question really should be: Data Blending. Rather than joining the tables, the tables should be blended.