3 Replies Latest reply on Apr 9, 2018 10:02 PM by Tushar More

# How to use calculation to compare number of records average to number of staff in region using parameter

Hello,

I have been using Tableau for a little while but I struggle to translate what I want to do into things I can search on the forums.

I am unable to attach the data I am using for privacy reasons but I hope I can explain my situation.

Each record is a complaint that has a Complaint ID and a higher level Case ID (a Case may have multiple Complaints) and so I have been using the number of records as the main measurement, and have arranged them by FY quarters. I have four regions - North, South, East and West - and I have researched the number of staff in each region and have included these as parameters.

Example below is (roughly) what my visualisation looks like at the moment:

RegionTotal Staff (Parameter)FY 2017 Q3FY 2017 Q4Grand Total
North300090110200
East250080110190
West19007065135
South20006560125

What I am trying to show is the amount of Complaints compared to the number of staff in a region, to illustrate the average Complaints generated per staff member. I have managed to create an IF THEN calculated field to show the staff numbers in a column but can't work out how to take the figures in the Grand Total column and use them in relation to the parameter (or vice-versa)... Any ideas?

• ###### 1. Re: How to use calculation to compare number of records average to number of staff in region using parameter

Hi Scott,

Not sure I have understood your question. However, to sum the two columns you can create a below calculation.

~Tushar

• ###### 2. Re: How to use calculation to compare number of records average to number of staff in region using parameter

Hi Tushar,

Thanks for getting back to me. I think my question is a bit confusing, sorry. Hopefully this info clears things up some more:

• I am trying to find the average amount of complaints per staff member in a given region (Grand Total/Total Staff).
• So in my diagram above, it would be to show a column that would equal complaints per total staff members in a region, so 0.067 for North (200/3000), 0.076 for East (190/2500) etc.
• The staff numbers I have had to add 'manually' by use of a IF THEN calculation using parameters of the numbers (i.e. IF Region="North" THEN [North Staff Number]...)
• I'd like to be able to add more quarters or change the time period of the visualisation and it be able to automatically update the complaints per total staff members figure.

Hopefully this helps better explain what I am trying to do...

• ###### 3. Re: How to use calculation to compare number of records average to number of staff in region using parameter

Hi Scott,

Thanks for the explanation. Here is my modified approach stored in the attached workbook v10.3.

~Tushar