Hello! I have a pivot table with two values in my rows, Sites, and types of Site, and then three values in my columns. One column value is a parameter that points to various performance metrics, let’s call it “Performance Metric”, the second column value is a period over period percent difference calculated field that refers to the performance metric parameter:
periodOverPeriodPercentDifference({Performance Metric},Date).
The third value in the columns is my Date field.
I have row and column totals turned on, and they are working correctly, however I’m not getting a value for my period over period field in the total field for the roll-ups by site type. I am getting the totals for performance metric rolling up properly, and I am getting the period over period calc aggregated by site, but not by site type. I’ve attached a screenshot and highlighted the blank areas in red, please let me know if this is something I can accomplish the way I’m approaching it or if I need to reconfigure!
Hello @joeFRUS, welcome to the QuickSight Community!
There are a few things to consider here. So you are calculating the percentDifference at the site level (lowest granularity in your visual) based on the Performance Metric compared to last year. Here is an area where QuickSight doesn’t have a way to roll up the total because the metric is running at the site level rather than the site type level.
Now, something you could do would be to apply that function in a KPI that would exist at the site type level next to your pivot table, and apply an action on your pivot table to change which site type it filters by. This would at least give your user a way to view those values at a different granularity. Alternatively, you could have a visual that has the drill down to the site level and a separate visual that contains just the site level to apply the calculation to both fields. I hope that makes sense and helps you understand why this issue is occurring. Let me know if you have any further questions!
Hello @joeFRUS, do you have any updates on the work-arounds I provided? If you found an option that works for you, please mark my above response as a solution. Otherwise, if you still have some questions on this, let me know and I can assist. Thank you!
