I’ve done a very simple table calculation e.g {one_measure}/{second_measure} and just want the raw data output but am only being given aggregation options in all visualisations. I’ve tried converting to dimension but still can only see the aggregated options.
I can do the exact same table calculation in Looker and get the raw data output but Quicksight only lets me aggregate the output. Can anybody advise?
Hi @datagirl
I guess as long as you havent all dimensions within your table visual you will get aggregated values.
e.g. dim1, dim2, dim3, value. With dim1,dim2, value it will be always aggregated for dim3.
BR
What visual type are you using? In a table visual you can just put all your fields in the Value field well and nothing in the Group by field well.
I’ve been using the KPI visual type and a basic table. I’m unsure why it needs any aggregation as a default. None of the aggregation options give me the desired result. In other visualisation tools a basic table calculation would be:
{one_measure}/{second_measure} = result
Quicksight is forcing an unnecessary aggregation:
{one_measure}/{second_measure} = aggregated result
have you tried summing those measures in the calculation sum({one_measure})/sum({second_measure}) ? That tends to help view things across different dimensions easier when I run in to issues like you’re having.
Hi @datagirl, an aggregation is necessary because you are effectively combining multiple rows of your dataset to generate the KPI visualization, whereas your calculation is at the row level. You can do what @oanielsen suggests and the aggregation method will show as “custom” rather than sum or average. However, you will get the same numeric result as you would if you set your current calculation to sum in the visualization UI (where your above screen shot is set to average). You can also try what @David_Wong suggests in a table visual which might make it more obvious what the aggregation in the KPI is actually doing.
This seems to override the need for aggregation but still feels unnecessary when I can do the same simple table calculation in other visualisation systems and get normal answers. Thank you!
Hello @datagirl, I have built KPI visuals similarly to this. I believe if you leave it as is with the calculated field {one_measure}/{second_measure} and then aggregate as a sum on the KPI, it should return the output as expected.
Similarly, you could do the aggregations within the calculated field, example:
sum({one_measure})/sum({second_measure})
Where sum can be swapped for the aggregation you need, and that will display it as a “Custom Aggregation” and will not allow you to change it.
I will mark this as the solution, but if you have any further questions on implementing this, please let me know!
