In a PivotTable, looking to determine the running utilization rate (active users / total users ; by group) percent for active users on a weekly basis.
Rows:
group_name (client name)
group_size (total users in the group)
Column:
first_completion_date (timestamp of their first activity; weekly aggregation)
Value:
user_email (count distinct aggregation)
Ideally, would like the calculation to return a percent that continually ticks up as more users in the group become active until they reach 100% utilization. Included a screenshot from Google Sheets and my formula on how I am manually creating this report for the time being.
Google Sheets Formula:
=SUM(‘Raw Utilization Data (Initial Topic)’!C5:$EX5)/‘Raw Utilization Data (Initial Topic)’!$B5
To calculate the running utilization rate, you can follow these high-level steps:
First, create a calculated field to determine the total time worked. You can do this by subtracting the starting time from the ending time.
Next, create another calculated field to determine the available time. You can do this by multiplying the total hours in a day by the number of days.
Now, create a calculated field to determine the utilization rate. You can do this by dividing the total time worked by the available time and multiplying by 100 to get a percentage.
To display the running utilization rate, you can create a table or chart in your Amazon QuickSight dashboard. Add the ‘Date’ field to the X-axis, the ‘Utilization Rate’ field to the Y-axis, and apply the ‘RunningSum’ function to the ‘Utilization Rate’ field.
By following these steps, you should be able to calculate and display the running utilization rate in your Amazon QuickSight dashboard.
Also, please use these resources from DemoCentral to help you frame the context:
