Most Frequent value in a column - through calculated columns

[PatCav]

Hi I need to do the below: 

1. Determine the most frequent text value in column (Answer) and display that in a calcualted column (C1)

2. Count the number of times the most frequent value occurs in column (Answer) and display that in a calculated column (C2)

Data example: 

Ref    Answer         C1                   C2

1       Reception     Reception         3

2       Manager       Reception         3

3       Reception     Reception         3

4       Reception     Reception         3

5       Clerk             Reception         3

I have to use calcualted columns for this. 

Any help is much appreciated!

[mickey64 · Accepted solution]

For your reference.

Step 1: I make a summarize table. (After Step 2, I add a column 'Number'.)

   Number = COUNTROWS(RELATEDTABLE('Data'))

    

Step 2: I add a relationship.

    

Step 3: I add 2 columns below.

    C2 = MAX('Summarize Table'[Number])

    C1 = LOOKUPVALUE('Summarize Table'[Answer],'Summarize Table'[Number],[C2])