Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Not applicable

## Pivot table - Average of medians

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 Replies
Not applicable
Author

Hi Danny,

You can use the following expression:

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

Former Employee

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))

)

Community Browser