Hello all,
I have a query that at the end needs to divide the subtotal of an item by its overall total.
I have groups based on user and that user has individual totals but then there is a final total for the entire user group.
For example, I have user one who has a total of 12 items and the total for the entire group equals 100. I need to find the percent of 12/100 (12% - User Total/Group Total).
Furthermore, I also need the average across all of the users involved.
For example:
User A: 12%
User B: 13%
User C: 14%
Average of all Users: 13%
I used the total function on the report page then attempted a layout calculation to do the division but received an error as the variable was invalid (guessing it didn't exist yet).
I also tried it as a query calculation, but I believe because I have the single data item within the query, it is always returning a value of 1.
Any advice on how to achieve this?
I'm guessing I'll likely need a join of some sort but am having some trouble getting there on my own.
Thank you in advance for any help you can give.
Regards,
sjdig
Quote from: sjdig on 19 Aug 2016 02:35:48 PM
Hello all,
I have a query that at the end needs to divide the subtotal of an item by its overall total.
I have groups based on user and that user has individual totals but then there is a final total for the entire user group.
For example, I have user one who has a total of 12 items and the total for the entire group equals 100. I need to find the percent of 12/100 (12% - User Total/Group Total).
Furthermore, I also need the average across all of the users involved.
For example:
User A: 12%
User B: 13%
User C: 14%
Average of all Users: 13%
I used the total function on the report page then attempted a layout calculation to do the division but received an error as the variable was invalid (guessing it didn't exist yet).
I also tried it as a query calculation, but I believe because I have the single data item within the query, it is always returning a value of 1.
Any advice on how to achieve this?
I'm guessing I'll likely need a join of some sort but am having some trouble getting there on my own.
Thank you in advance for any help you can give.
Regards,
sjdig
Hi
Have you tried a query calculation with the expression
total([your Count measure] for [your User item]) / total([your Count measure] for report)
MF.