How to calculate a Weighted Mode

[hansei]

Using the following example table

Units	Unit Price
1	100
2	300
1	100
3	300
1	100

While getting the weighted mean of unit price is fairly trivial

SUMX( Table, [Units] * [Unit Price] ) / SUM( Table[Units)
= 1800 / 8
= 225

I am trying to figure out the weighted mode which should equal 300, and am at a loss. Please help 🙂

[amitchandak]

@hansei , what is the logic for 300

[lbendlin]

@amitchandak  Mean, Median, Mode Calculator (calculatorsoup.com)

[lbendlin · Accepted solution]

Here's the long way of doing this:

Mode =

var a = ADDCOLUMNS(values('Table'[Unit Price]),"pr",'Table'[Unit Price])

var b= ADDCOLUMNS(a,"ct",CALCULATE(sum('Table'[Units]),filter('Table','Table'[Unit Price]=[pr])))

var c = topn(1,b,[ct],DESC)

return CONCATENATEX(c,[pr],",")

here's a slightly shorter version:

Mode =

var c = SUMMARIZE('Table','Table'[Unit Price],"ct",sum('Table'[Units]))

return CONCATENATEX(TOPN(1,c,[ct],DESC),'Table'[Unit Price])

and here is a really nice one:

Mode = LASTNONBLANK(SUMMARIZE('Table','Table'[Unit Price]),sum('Table'[Units]))

[hansei]

@lbendlin, thanks for that. Your third example does not actually do a weighted average, as summarized is unordered. But the second one is cleaner than what I had actually ended up using:

Mode = MINX(
    TOPN(
        1, 
        ADDCOLUMNS(
            VALUES( Table[Unit Price] ),
            "Frequency", 
            CALCULATE( SUM( Table[Units] ) )
        ), 
        [Frequency], 
        0        
    ), 
    Table[Unit Price]
)

[lbendlin]

That's actually a really good observation - I will need to investigate if SUMMARIZE() does any sorting or not. I thought it does 🙂