2 Replies Latest reply on Apr 15, 2018 3:17 AM by Jeevan Krishna

# Tool Availability Calculation Help

Hi,

I am working on a project in school that requires to calculate Tool Availability in Tableau and I am having some difficulties.

The formula for Tool Availability: ( 24 hours * the number of machines -  Sum(Downtime) )/ (24 hours * the number of machines)

Here is what the raw data looks like:

Production LineMachine Downtime (hours)
A12
A21
B13
B24

#1 I have tried this calculated field: ( 24*[Number of Machines] - SUM(Downtime) ) / ( 24*[Number of Machines] )

but this does not work because Tableau does not aggregate the values together for non-sum.

#2 I have also tried this: ( SUM(24*[Number of Machines] - SUM(Downtime) ) / SUM (24*[Number of Machines])

but the problem is this only sum up the number of machines that show up on the data (which makes sense because only the machine that has downtime would appear in the SQL data).

In summary, the number of machines at each production line is fixed. For example, Line A has 8 machines so the total available machine hours is 8*24 = 192 hours but if on that day, there are only 2 machines that are down and I use #2 calculation, it does not give the right number.

Production LineMachine Downtime (hours)Tool Availability
A12= (24 hours * 4 machines - (2 hours + 1 hour) )/ (24 hours * 4 machines) = 96.87%
A2196.87%
A30 downtime so will not show up in the SQL data
A40 downtime so will not show up in the SQL data
B13= (24 hours * 2 machines - (3 hours + 4 hours) )/(24 hours * 2 machines) = 85.42%
B2485.42%

Also, how should I store the number of machines in each production line (it is fixed) in the Tableau data?

Thank you!