I am trying to show a basic table, but have problems with showing correct totals.
My dataset consist of rows for each person that has signed in and each person has a group. For each group I know how many actual group members there are (but not all signed in yet).
person 1, Group1, 50
person 2, Group1, 50
person 3, Group 2, 100
My table should look like this, I have problems getting the red marked values in Quicksight:
For Groupmembers I would like to get as total the sum of all group members, and in each row the number of the group members.
For correct rows I need to use average aggregation (as each row actually aggregates multiple rows of my dataset), but then the total value is wrong (as it calculates some weird average)
Signed in is a count of rows - this works fine
For percentage I use Signed in/avg(Groupmembers) - here the same problem occurs, I assume if the first is fixed, I can resolve this as well
If this does not work this way, I would also be grateful for tips how to change my dataset in a way that this works (one dataset includes person & group, the other group & number of groupmembers)
Thanks in advance!