topic Re: Calculate median over grouped data in App Development

Calculate median over grouped data

marcin_st — Tue, 26 Mar 2024 10:55:03 GMT

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?

Re: Calculate median over grouped data

bgerchikov — Wed, 27 Mar 2024 01:54:43 GMT

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

Re: Calculate median over grouped data

JonnyPoole — Wed, 27 Mar 2024 02:49:37 GMT

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;