Calculate Mode Range (the most common values within a range)

[Tomas_Seig]

Hi all,

Thank you very much in advance for your support.

I am trying to calculate given a list of prices, the Mode within a 20EUR range so as to say, the most common price is between 120-140EUR.

To calculate the Mode I am using the measure:

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

Any ideas how to add 20 or subract 20 or any possible combination in between that would include the most hits?

Kind Regards,

Tomas

[Tomas_Seig · Accepted solution]

Solved like this:

RANGE = COUNTROWS(FILTER(FT_SALES_TRADING_BI,(FT_SALES_TRADING_BI[PRICE]>= EARLIER(FT_SALES_TRADING_BI[PRICE]) && FT_SALES_TRADING_BI[PRICE] < EARLIER(FT_SALES_TRADING_BI[PRICE]) +20 )))

[daXtreme]

Any ideas how to add 20 or subract 20 or any possible combination in between that would include the most hits?

What do you mean by this? Calculating the mode(s) is rather simple. For each value in your set you just look at the number of any specific values and the one(s) that are/is the most frequent are/is the mode(s).

[Tomas_Seig]

Sorry, I dont think I explained myself correctly. What I need is the most common subset of prices with length 20.

So, given a list of 100 sales, count how many fall within a 20EUR range and get the range with the highest count.