Calculate percent of total taking duplication into account

Question

I have the following table containing users and the devices that they use

+--------+--------+
| UserId | Device |
+--------+--------+
| user1  | PC     |
| user1  | TV     |
| user2  | TV     |
| user2  | Phone  |
| user2  | Phone  |
| user3  | Phone  |
| user4  | PC     |
| user5  | Phone  |
+--------+--------+

I want to find the percentage of user using a given device. If I use percentOfTotal(count(UserId), [Device]), the result will be as follows:

+--------+----------------+
| Device | Usage rate     |
+--------+----------------+
| PC     | 25%            |
| TV     | 25%            |
| Phone  | 50%            |
+--------+----------------+

However, this result is not what I want since a user can use more than one device. In my opinion, the usage rate should be calculate as (count distinct users using the same device) / (count distinct all users), i.e. the result should look like this:

+--------+----------------+
| Device | Usage rate     |
+--------+----------------+
| PC     | 40%            |
| TV     | 40%            |
| Phone  | 60%            |
+--------+----------------+

I wonder if I can calculate that using AWS Quicksight

Answer 1

At the moment you can define a measure that returns the number of distinct users for each device but not the total number of distinct users. Once we add ability to get total number of distinct users, you should be able to do everything in QuickSight. We are hoping to add this soon. Current workaround is to make changes in the data prep or use custom SQL to provide number of distinct users in the dataset.