10 Replies Latest reply on Jul 27, 2017 1:10 PM by Galen Busch

# Finding maximum occurrence of data (MODE)

Hi Tableau users,

I want to find the maximum occurrence of data of rows, in statistical terms its the mode of the row. I want to return the the value or highest mode.
I will not able to share the data due to company policy but i am attaching the screen shot that what i need.

In the given picture below i want to find the "mode" for baseline discount of each contract criteria. Currently i am doing the average of baseline discount but i want to return the value of baseline discount which have highest number of mode for each contract criteria.

Hope i get the solution from this community.

Thanks

• ###### 1. Re: Finding maximum occurrence of data (MODE)

Hi Abhishek,

The table calculation to return mode is:

SUM([Number of Records])=WINDOW_MAX(SUM([Number of Records]))

You can modify this to level of detail - I'm not sure what your grain is -

SUM([Number of Records])=MAX({INCLUDE [Dimensions] : sum([Number of Records])})

• ###### 2. Re: Finding maximum occurrence of data (MODE)

Thanks Galen For your solution,
But i am getting boolean value when i use the above calculation. I  want the mode for baseline discount for each contract criteria.

• ###### 3. Re: Finding maximum occurrence of data (MODE)

I am not sure I understan. From the screenshot, what is the expected result? 8 occurance of 72.58?

• ###### 4. Re: Finding maximum occurrence of data (MODE)

Hi Abhishek,

Let's say Contact Criteria is your level of detail.

for "DAW 5" you have 5 values -

as percents, they likely appear as decimals in your data -

.5

.6

.6

.7

.8

You want the mode.

Using:

SUM([Number of Records])=MAX({INCLUDE [Contact Criteria] : sum([Number of Records])})

This will generate:

.5 - False

.6 - True

.6 - True

.7 - False

.8 - False

So essentially what you want is .6 in this instance.

IF SUM([Number of Records])=MAX({INCLUDE [Contact Criteria] : sum([Number of Records])}) then avg([Baseline Discount] END

This returns the baseline discount (the average of the mode value, which will be the same as the max, or min.)

Does that help?

• ###### 5. Re: Finding maximum occurrence of data (MODE)

It is working but the problem i am facing is suppose my data has  following numbers

.3

.4

.4

.5

.6

.6

.6

.7

so in the above data mode will be 2 for .4 and 3 for .6 . If we apply average then result will be .50 in the 2, but i want the result should reflect the maximum occurrence  value(or mode that has max value) in this case i want .6 as the output as it has mode of 3.

• ###### 6. Re: Finding maximum occurrence of data (MODE)

In your example above, the mode should still be .6, as it occurs 3 times (equal to the max) and .4 occurs 2 times (not equal to the max.)

It would however malfunction if there were 2 modes - you'd have to write some logic in to determine which mode to select (a higher mode, or lower mode) by max/min.

• ###### 7. Re: Finding maximum occurrence of data (MODE)

Hi Abhishek,

Check out this article and let me know if that helps.

Thanks,
Mani

• ###### 8. Re: Finding maximum occurrence of data (MODE)

what will happen if  i have .6 for 3 times and also .4 for 3 times

• ###### 9. Re: Finding maximum occurrence of data (MODE)

Hey Galen,

I am attaching the screen shot. In the pic i showed what i am getting for mode according to your calculation (i.e Remedy Negotiated 2) and i have mentioned beside that what i want.
Can resolve this problem ?

• ###### 10. Re: Finding maximum occurrence of data (MODE)

You'll have to pick a mode via aggregation. There is not a standard way to calculate the mode when multiple possible values are returned. The answer is that the data set has 2 modes. You could average the 2 or more modes, you could MIN() or MAX() the mode (return .4 or .6.)