Forum Discussion
Most Frequent value in a column - through calculated columns
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!
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])
1 Reply
- mickey64
Super User
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])