Calculating mode of a measure

[Anonymous]

Hi.

I'm trying to calculate the mode off of a measure. The problem is that the measure is calculated off a user selceted time period.

e.g. The measure creates the following totals based on a user selected period of 6 days:

Day 1       Day 2       Day 3       Day 4       Day 5       Day 6

  2              4              7              7              3              4

I then need the mode of this series, which in this example would be 7.

Interested in any ideas.

Thanks.

[OwenAuger · Accepted solution]

Hi @Anonymous

Here are a couple of possible patterns adapted from DAX Patterns - Mode
(the pattern on DAX Patterns calculates the mode of a physical table column):

Mode of Measure by Granularity Column =
MINX (
    TOPN (
        1,
        SUMMARIZE (
            ADDCOLUMNS ( VALUES ( <Granularity Column> ), "Measure", <Measure> ),
            <Measure>,
            "Frequency", DISTINCTCOUNT ( <Granularity Column> )
        ),
        [Frequency]
    ),
    [Measure]
)

Mode of Measure by Granularity Column v2 =
MINX (
    TOPN (
        1,
        GROUPBY (
            ADDCOLUMNS ( VALUES ( <Granularity Column> ), "Measure", <Measure> ),
            [Measure],
            "Frequency", SUMX ( CURRENTGROUP (), 1 )
        ),
        [Frequency]
    ),
    [Measure]
)

In your case, replace <Measure> with your measure and <Granularity Column> with the Day column.

The MINX is an arbitrary tie-breaker. Could equally be MAXX.

Does that work for you?

[Anonymous]

Hi @OwenAuger

I've tried this formula with mi data set and it is not working...

I'm trying to find the most frequent average buying price of an antricle per store.

Once I introduce the stores to the table I get the same average price per store as the most frequent one...

And I can't make the deviation.

Could you please help me out?

THX

[Anonymous]

Thanks for the assist Owen.

Solution seems simple now I think about it. Teach me for trying to do this at 11PM at night 🙂

[mpo]

Hello,

You could try adding a date-value table just for this.

For example, in Power Query, create a table with unique dates from your existing table.

Then add calculated column with the calc of your measure,

Then getting mode should be trivial.

[KHorseman]

@Anonymous http://www.daxpatterns.com/statistical-patterns/#mode