Calculate median of aggregated data

[s45kougo]

Hi,

Hoping someone can help as I can't find any information on this...

I want to calculate the median price for some pre-aggregated data - I don't have access to the line-level data.

A simplified table looks like this:

Total Spend	Quantity	Price
25	5	5
20	2	10
40	10	4

This should give a median of 4.  But how do I write that calculation?

Thanks in advance

[Greg_Deckler · Accepted solution]

@s45kougo - Here is a much more elegant solution, Page 32, PBIX Table (32) attached below sig.

Measure 32 = 
    VAR __Table =
        GENERATE(
        'Table (32)',
        VAR __Quantity = [Quantity]
        RETURN GENERATESERIES(1,__Quantity,1)
    )
RETURN
    MEDIANX(__Table,[Price])

[s45kougo]

Oh wow, you are amazing , thank you *so* much! This works perfectly. I thought it must be possible, but I had no idea how to disaggregate a table. Hope this helps someone else too in the future 🙂

[Greg_Deckler]

@s45kougo - There may be a more elegant solution and I will think about it but for this you could brute force it:

Measure = 
  VAR __Table25 = 
    ADDCOLUMNS(
      GENERATESERIES(1,MAXX(FILTER('Table',[Total Spend]=25),[Quantity]),1),
      "Price",MAXX(FILTER('Table',[Total Spend]=25),[Price])
    )
  VAR __Table20 = 
    ADDCOLUMNS(
      GENERATESERIES(1,MAXX(FILTER('Table',[Total Spend]=20),[Quantity]),1),
      "Price",MAXX(FILTER('Table',[Total Spend]=20),[Price])
    )
  VAR __Table40 = 
    ADDCOLUMNS(
      GENERATESERIES(1,MAXX(FILTER('Table',[Total Spend]=40),[Quantity]),1),
      "Price",MAXX(FILTER('Table',[Total Spend]=40),[Price])
    )
  VAR __Table = UNION(__Table25,__Table20,__Table40)
RETURN
  MEDIANX(__Table,[Price])

[amitchandak]

@s45kougo , In case this data is already aggregated. You can average

Avg = Divide(Sum(Table[Total Spend]),sum(Table[Quantity]))

Which will be five

You can try MEDIAN( Table[Price] ) 

But I doubt you can get 4

[s45kougo]

@amitchandak thanks for trying, but I do want the actual median of 4... I'm guessing that somehow I have to calculate a table that dis-aggregates, but not sure how to do this.