Measure to calculate most repeated value in a column

Ignpl 0
Viewed 36

I'm working with a Soccer League Database with a table like this:

Soccer League Table

I would like to create a measure to calculate the most repeated Division that every team has played in all the seasons. For example: For Team A it will be 1 and for Team B 3

Thank you! Regards

dax powerbi
2 Answers

You can achieve this with the following expressions:

  1. Create a calculated table counting the frequency of each division
Frequency = 
SUMMARIZE(
    Soccer,
    Soccer[Team], Soccer[Divison],
    "Number", Count(Soccer[Divison])
)
  1. Add a calculated column that ranks the frequencies per team
Rank = 
RANKX(
    FILTER(
        ALL('Frequency'),
        Frequency[Team] = EARLIER(Frequency[Team])
    ),
    'Frequency'[Number]
)
  1. The resulting calculated table showing teams and top divisions
Top Division = 
SELECTCOLUMNS(
    FILTER(
        Frequency,
        Frequency[Rank] = 1
    ),
    "Team", Frequency[Team],
    "Division", Frequency[Divison]
)
userhiPt0

Assuming a table named Table1, this measure:

MostRepeatedDivision :=
VAR T1 =
    SUMMARIZE( Table1, Table1[DIVISION], "Count", COUNTROWS( Table1 ) )
VAR MostRepeated =
    MAXX( T1, [Count] )
RETURN
    MAXX( T1, IF( [Count] = MostRepeated, Table1[DIVISION] ) )
Jos Woolley0
Related