Pivot table - Average of medians

[#1]

Hello,

My data table looks like this:

class	week	sku	sales
a	1	100	10
a	1	101	30
a	1	102	50
a	2	100	100
a	2	101	200
a	2	102	300
a	3	100	1000
a	3	102	2500
a	3	103	3000

I need to create a pivot table with two dimensions (Class and Week), and one expression (Sales).

The expression gives me the MEDIAN value of Sales, by week.

My pivot table looks like this:

class	week	median
a	1	30
a	2	200
a	3	1000

My problem is that when I collapse the Week dimension, I need to see the AVERAGE of the weekly MEDIANS, which in the case of this example is 410. In other words, I need to see the average of the rows in the pivot table (the average of the median values for week 1, 2 and 3 , which is the average of 30,200 and 1000)

class	week	median
a		410

The problem is that when I collapse the week  dimension I get the median of the data instead, which in this case is 200. Notice that my goal is to see AVERAGE of the weekly MEDIANS , as opposed to the total average of the data, which is 798.

Any idea?
Thanks

[#2]

Hi Danny,

You can use the following expression:

avg(aggr(median(sales),week))

[JonnyPoole]

If you need a dynamic solution.  Medians when expanded but avg of medians when collapsed i used this to get that effect:

if( isnull(aggr(Median(  sales),week))<>-1,aggr(Median( total <week> sales),week),avg( total <class> aggr(Median( sales),week))

)