I have a spreadsheet that looks like this:
Country | Choice |
---|---|
Brazil | Rock |
Brazil | Rock |
Brazil | Paper |
Peru | Scissors |
Peru | Scissors |
Peru | Rock |
Cuba | Paper |
In the Data Studio report, how do I select the most frequent value for each country? The expected result is:
Country | Choice |
---|---|
Brazil | Rock |
Peru | Scissors |
Cuba | Paper |
In Google Sheets this can be done with this formula:
=INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)),COUNTIF(A1:A7,A1:A7),0))
But Google Data Studio does not support INDEX
A pivot table could be used, with a limit of 1 row in the Choice
field so as to see the "Top 1" value for each Country
:
Country
Choice
Record Count
Country
, alphabetically):Country
COUNT
, from highest to lowest):Record Count
There were 2 NULL
values in the Country
field, which can be hidden using the filter:
Excludes `Country` Is NULL
The default fields at the data source are:
The GIF uses the field names in the question, which uses shortened versions of the names used in the data set (this was done by renaming the respective fields at the data source, so as to keep the original names in the data set as is):
What country are you from?
was renamed to Country
Choose one
was renamed to Choice
Also, The Record Count
field is an auto generated field created in certain connectors such as Google Sheets and BigQuery, which serves the function of COUNT
(Field)
Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to elaborate: