Dividing data piece by total from report and achieving group average

[sjdig]

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

[MFGF]

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.