Search code examples
datasetlocationtableau-apicalculated-field

Display most frequent value in Tableau per location

I am trying to show the most frequent employee number per location.

For an example, if 10 is the most frequent number in my dataset relating to New York, I would like "10" to show up as the value next to New York. I have several other locations so is this possible?

I have tried to create a calculated field but it did not work for me.

How would I go about doing this?

Solution

  • Most frequent value i.e. MODE is calculated for a dimension normally. In tableau mode is also calculated for a dimension. However, for your use case, you may do a workaround like this-

    For the following workaround I have created a sample data like this

    enter image description here

    Step-1 Create a calculated field say CF1 with the following calculation

    { FIXED [City] : Max ({FIXED [City], [Measure]: COUNT([Measure])})}
=
{FIXED [City], [Measure] : COUNT([Measure])}

    Step-2 Convert the Measure into dimension (by right clicking it). If you require it as a measure for some future aggregation (create a copy of this field)

    Step-3 Add city and measure on rows shelf.

    Step-4 create a filter on CF1. (Now when TRUE values are filtered only the most frequent value in Measure for each city will only be displayed. The following screenshots will help in explaining the things.

    enter image description here

    and

    enter image description here