Mode result with multiple "right" answers

[OscLar]

Hi,

I have yet antother question regarding the mode calculatuion. I'm currently using this function:

Mode :=
MINX (
    TOPN (
        1,
        ADDCOLUMNS (
            VALUES ( Data[Value] ),
            "Frequency", CALCULATE ( COUNT ( Data[Value] ) )
        ),
        [Frequency],
        0
    ),
    Data[Value]
)

In addition I'm also ading some filters on the 'Data'-table but on a differenct column, one containing dates.

Now, in my case I come across several times where there are several values in 'Data'[Value] which corresponds to "the most probable value. Example se the following short table:

[Value] - [Count]

0.49 - 1

0.50 - 2

0.55 - 1

0.66 - 2

0.75 - 1

0.80 - 1

0.95 - 2

In the above table the Mode measure will return 0.50 as the most probable value. Now as you can see 0.50, 0.66, and 0.95 are all equally frequent.

Now, is there a way to force the Mode measure to return the last value instead of the first it encounters. I.e. In the table above I would like to get the answere 0.95 not 0.50??

Cheers,

OscLar

[Zubair_Muhammad · Accepted solution]

@OscLar

If the last value means the MAX value you can simple change MINX to MAXX

Mod =
MAXX (
    TOPN (
        1,
        ADDCOLUMNS (
            VALUES ( Data[Value] ),
            "Frequency", CALCULATE ( COUNT ( Data[Value] ) )
        ),
        [Frequency], DESC
    ),
    Data[Value]
)

[OscLar]

Hi @Zubair_Muhammad

WTF, how could I have missed that?!? Well thanks for pointing that out. 🙂

Cheers,

OscLar