Finding median in grouping with 2 criteria

[alandilworth]

Hello, I need some help finding median values in a double grouping.  Not sure if this is the best description, but in the table below I am trying to find the median ‘WAC’ value for every ‘GaltTerritory’ belonging to each ‘GaltOwner’.  I am having trouble with this since a 'GaltTerritory' can exist for more than one 'GaltOwner'.

For instance, in the table provided the desired results would be:

Southside            10101    2165.00
White Space       10101    1560.00
PharmedRx IV   10101    2.00
PHARMEDRX I    10101    2350.00

I am able to calculate the median based purely on ‘GaltTerritory’, however, this is undesirable since some ‘GaltTerritory’s (as shown in my table) have belonged to different ‘GaltOwner’ at different time periods.  Because of this, I need to find the median of every ‘Galt Territory’ for each ‘GaltOwner’

Any insight will be greatly appreciated!

[Or]

What I understand from your description is that you want the median value for each intersection of your two table dimensions, but as far as I know, that's what Median(WAC) would do in a table where those two dimensions are used. Also, this doesn't seem to match your desired results table, since the median for Southside / 10101 appears to be 2165, not 2340 as you describe. Could you perhaps further explain what exactly you're trying to achieve?

[alandilworth]

You are correect, Southside 10101 should be 2165.  I did an incorrect manual calculation.  I have tried using median(total <GaltTerritory> WAC) as a calculated field.  It works for all cases except when a 'GaltTerritory' belongs to more than one 'GaltOwner'.  So for 10101 I do not get correct results using this method.

[Or · Accepted solution]

But why not just use Median(WAC) with GaltTerritory and GaltOwner being the two dimensions?

In any case, you should also be able to use median(total <GaltTerritory , GaltOwner> WAC) I think. Total < Field > should be able to take multiple fields:

median({[SetExpression] [TOTAL [<fld{, fld}>]]} expr)

[alandilworth]

Upon further review, you are correct Median(WAC) is returning correct results.  I was sure I tried that previously with wrong results but after trying it again it is definitely working. 

I appreciate your help!