Calculating Average of Maximum Progress Per Session in a QuickSight KPI

Question & Answer
, ,
1

Hi everyone,

I’m trying to create a KPI in Amazon QuickSight that shows the average of the maximum progress per session. Each session has multiple progress entries, and I want to first find the maximum progress for each session and then take the average of those maximum values across all sessions.

Here’s what I’ve tried so far:

  1. Using maxOver to Calculate Maximum Progress Per Session:

  • I created a calculated field called Max Progress per Session using:

    maxOver({progress}, [{session_id}], PRE_AGG)

  • This correctly gives me the maximum progress for each session but retains a row for each entry in the session, not just one unique row per session.

  1. Attempting to Use sumOver with maxOver:

  • I tried to use sumOver(maxOver({progress}, [{session_id}], PRE_AGG), {session_id}) to calculate the sum of unique maximum progress values per session, but I encountered an error:

    Expression sumOver(maxOver({progress}, [{session_id}], PRE_AGG), {session_id}) has incorrect argument type sumOver(Number, String). Function syntax expects Numeric, List, Calculation Level.

  • And if I add PRE_AGG it sums the duplicated rows, so this approach didn’t work.

  1. Using denseRank for Deduplication:

  • I also tried to use denseRank to rank the entries within each session and keep only the first row per session:

    denseRank([{session_id}])

  • However, I encountered syntax errors when trying to use denseRank with a specific sort order, like denseRank([{session_id}], [{progress} DESC]).

  1. Current Status:
  • I can display the maximum progress for each session, but I haven’t found a way to calculate the average of these maximums without QuickSight duplicating rows within each session.
  • I tried adding Max Progress per Session to the KPI and then calculating avg({Max Progress per Session}), but this averages all rows, leading to incorrect results because of the retained rows for each session.

What I’d like is for the KPI to display the correct average of unique maximum progress values per session. Has anyone managed to achieve this in QuickSight or know of a workaround for this kind of nested aggregation or distinct averaging?

Thanks in advance for any suggestions!

Arena Example AVG Max Progress

1 Like
2

Hello @Moshe, welcome to the QuickSight community!

Here is a link to the updated Arena dashboard that I worked on: Calculating Average of Maximum Progress Per Session in a QuickSight KPI

The bottom KPI should be returning the number you are looking for! You were on the right track with the denseRank calculation, you just had the wrong order of operations within it. Basically, I just made it rank the highest progress value per session as rank 1 and then filtered out all values that aren’t equal to 1. This allowed the KPI to return the correct value for average. I’ll mark my response as the solution, but please let me know if you have any follow-up questions and I am happy to assist you further!

3

Hi Dylan,

Thank you for the warm welcome and your assistance with this issue! The dashboard you shared works perfectly, and I’m now getting the correct average.

I appreciate the clarification on the denseRank calculation and the correct order of operations, I hadn’t considered filtering to only keep the rank 1 values. This approach made all the difference, and I’ll keep it in mind for future calculations.

Thanks again for your help.

1 Like
4

Hello @Moshe,

I am glad that provided you with the solution you were looking for. Thank you for following up!

1 Like