I am interested in creating a calculated field that results in a ratio of the total assignments segmented by Client. For example, if I was wondering what the ratio of assignments Widget Works, Inc. had in relation to the total number of assignments handled. The Client pill will be in the Pages shelf so each page will be segmented by Client.
I know this calculation is wrong, can you show me the right way to write it? Assume that there are at least two data field named 'Client,' with client's names and 'Assignments' with a numeric representation of assignments.
Assignments.Client / Assignments
The idea behind this is to create a ratio from the assignments of a client and then transfer that ratio to the total expense of fulfilling all assignments.
So if a client sent over 1 assignments out of 10 total assignments, and total expense is $20, then the result for that client would be $2.
Something like the following, even though I know the syntax is wrong: ( Assignments.Client / Assignments ) * Expense
I did receive some help on the LinkedIn forums and was able to get the calculation right:
SUM([Invoice])-(COUNTD([Case Number])/TOTAL(COUNTD([Case Number]))*SUM([QUICKBOOKS.xlsx].[Amount]))