Calculate median over grouped data

[marcin_st]

Hello all,

I'm struggling to write a formula in a KPI chart which will calculate the correct median for the following dataset:

RecordID	Price	Counter
100	200	1
101	250	1
102	300	2

If we use the simple Median(Price) it will give us the result of 250 but the correct value should be 275 because the "PriceCount" column tells us how many times this price exists in the data. Tried to write something like this but it does not return a correct value:

Median(aggr(if(RangeSum(Above(Counter)) <= Count(total ID) / 2
        and
        RangeSum(Above(Counter)) + Counter >= Count(total ID) / 2,Price),ID))

Could you please try to help me understand and solve this problem?

[bgerchikov]

I'm not sure how, but your formula works correctly:

[JonnyPoole]

It may be possible trying to do this with concat() and generating a long list of values on the fly, but it feels like performance and scalability might lag trying to do this all in the UI, and to me the approach feels like a stretch. 

I think everything works way better if you uncompress your data and create a data set like this:

Then its easy to apply the median() function 

To uncompress the data, here is a script that duplicates the data row by row as determined by the counter value. Also attached is the XLXS I used to generate this.

CompressedData:

LOAD

    RecordID,

    Price,

    Counter

FROM [lib://Community Answers:DataFiles/2434655.xlsx]

(ooxml, embedded labels, table is Sheet1);

LET vNumRows=NoOfRows('CompressedData')-1;

FOR row = 0 TO $(vNumRows)

LET vRowsToGenerate=PEEK('Counter',$(row),'CompressedData');

    UnCompressedData:

    LOAD

    PEEK('RecordID',$(row),'CompressedData') AS RecordID,

    PEEK('Price',$(row),'CompressedData') AS Price

AutoGenerate $(vRowsToGenerate);

NEXT row

DROP TABLE CompressedData;