I can't figure this formula out and I am going round in circles so I put myself at your mercy.
Consider the following table
ClientCode
EmployeeCode
Employee Visits
total client visits
total number employees
result
1
a
4
88
5
-0.02%
1
b
8
88
5
0.61%
1
c
6
88
5
0.24%
1
d
1
88
5
-0.22%
1
e
69
88
5
61.96%
1
Total
88
88
5
62.57%
I Just need to calculate that one figure in the bottom right which is the sum of the percentages above it, note it can be expected that there will be a number of ClientCodes and the result should be calculated for each inidividually
End result should be something like:
ClientCode
Result
1
62.57%
2
12.58%
The formula are quite simple, given the dimensions ClientCode and EmployeeCode
Employee visits = count({<whole bunch of set>} VisitCode)
Total Client visits = count({<whole bunch of set>} TOTAL <ClientCode> VisitCode)
Total Number Employees = count({<whole bunch of set>} DISTINCT TOTAL <ClientCode> EmployeeCode)
