Showing correct Totals

Question & Answer
1

Hi,

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).

EG
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:
image

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!

2

Assuming you have a table with UserID, GroupID, LoggedIn as 1 or 0 uyoi can

Convert the table to a pivot table. Put person under group in the rows. Use average (loggedIn ) as the value column –
The value column should work the way you expect at the member, group, and overall level.

3

Hi - Thanks for your anwer - unfortunately that did not work, but I managed to find a work around with SumOver