I have a table with fields -
I want to add a calculated field sum_Brekadown_KPIs which sums up the average of each KPI across the iterations for each breakdown. For example the value of sum_Brekadown_KPIs for SIP Delay would be avg(val) across iterations 1-3 for KPI A + avg(val) across iterations 1-3 for KPI B which is
avg (2.4, 3.7, 4.1) + avg(6.7, 4.5, 7.8) = SIP Delay
How to compute this calculated field in quicksight using sumOver or avgOver? Can you please help me?
Hello @tpanati - Thank you for posting your query. I have tried to replicate the use case at my end and took a shot at it. Sharing the same below for your reference.
Step 1 : Created a sample dataset based on the sample that you provided
Step 2 : Created 2 calculated fields as highlighted in the below snapshot with one referring other
Step 3 : The Final field named “Sum of Average KPIs” is the field which contains the expected value.
Hope this helps! Thank you!
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
Hi @sagmukhe
Thank you so much for the suggestion. It works when there are multiple iterations for each KPI!!
But looks like it is not working as expected when there is just one iteration for each KPI -
In this “Average by KPI” must be same as the Val column right because there is only one iteration. But as per this calculated field it computes a different value.
Can you please help me?
@tpanati - Yes, that should provide the same value and when I have replicated the same I am indeed getting the same value without making any changes in the calculations. Please see the below snapshot for reference where “Value” and “Average by KPI” columns share the same values as expected. Would request you to double check at your end. Hope this helps! Thank you!
Hi @sagmukhe
Thank you for checking it out. I had made some mistake in the calculation where the Average by KPI depends on another variable called batch label as well. Because I didn’t include it, I was getting random values when there is only one iteration per KPI. Fixed it!
Thank you once again for the guidance
