Hello Team ,
We have the following data available in this format
But we need to build a visual in the following format as show below
Hello @DeepikaChoda, do you have permissions to write custom SQL to query your database into a new dataset? What you are wanting to complete in QuickSight is a complete restructure of the data that you are utilizing. You could use the data you have currently to build a KPI to display the Initial Average, but you can’t create new rows in a table view like you are expecting.
You are looking for the PIVOT/UNPIVOT functionality that is available in SQL to accomplish this table view.
This function avgOver({Apple_initial}, [], PRE_AGG) would give you an average value across the full Apple_initial column, but to achieve the table output you will need to build a new dataset that groups by the metric of Apple, Mango, or orange rather than at the ID level. Let me know if you have any further questions!