powerbidaxfrequency

Power BI DAX: Calculate mode based on conditio

Name Day Sales "Global" mode Mode, gruoped on "Name"
Matt 1 100 100 100
Matt 2 100 100 100
Matt 3 100 100 100
Matt 4 100 100 100
Matt 5 100 100 100
Matt 6 100 100 100
Matt 7 95 100 100
Matt 8 90 100 100
Andrew 1 99 100 99
Andrew 2 99 100 99
Andrew 3 99 100 99
Andrew 4 99 100 99
Andrew 5 99 100 99
Andrew 6 94 100 99
Andrew 7 94 100 99
Andrew 8 89 100 99

I want to calculate the mode (most frequent value) of sales, grouped on "Name".

I want to calculate it as a column, as shown in the table.

The mode of all values is calculated like this:

"Global" mode = 
VAR T1 =
    SUMMARIZE( Table1, Table1[Sales], "Count", COUNTROWS( Table1 ) )
VAR MostRepeated =
    MAXX( T1, [Count] )
RETURN
    MAXX( T1, IF( [Count] = MostRepeated, Table1[Sales] ) )

Source: Measure to calculate most repeated value in a column

i tried to modify the formula for the global mode, but was unsucsessful.

Solution

  • Try something similar to:

    Mode per Name =
  var perName = 
    CALCULATETABLE(
      ADDCOLUMNS(
        DISTINCT(Table1[Sales]),
        "fq", CALCULATE(COUNTROWS(Table1))
      ),
      ALLEXCEPT(Table1, Table1[Name])
    )
  return
    MINX(
      TOPN(1, perName, [fq], DESC),
      [Sales]
    )

    For what's it worth - Power Query does have a List.Mode function and would be a better place to add these columns.