Search code examples
looker-studio

How can I select the most frequent text value in each group?

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

Solution

  • 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:

    1) Pivot Table

    1.1) Fields

    • Row Dimension #1: Country
    • Row Dimension #2: Choice
    • Metric: Record Count

    data_tab_fields

    1.2) Sorting

    Row #1 (sorts Country, alphabetically):

    • Field: Country
    • Order: Descending
    • Number of rows: Auto

    Row #2 (sorts by COUNT, from highest to lowest):

    • Field: Record Count
    • Order: Descending
    • Number of rows: 1

    data_tab_sorting

    2) Filter

    There were 2 NULL values in the Country field, which can be hidden using the filter:

    Excludes `Country` Is NULL

    filter_excludes_country_is_null

    3) Hide Metric Column

    • One way to hide the metric column from viewers is to draw a shape such as a rectangle, over the respective area and then match the colour to that of the background (which is white in this case).
    • Another approach (used below) is to simply reduce the width of the pivot table, starting from the side of the metric (right); this method ensures that the scroll bar is also visible to users

    Additional Notes

    The default fields at the data source are:

    default_fields_at_data_source

    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

      renamed_fields_at_data_source

    • 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:

    GIF