I am attempting to get Median values on data that I have already aggregated and am getting an error that says the argument is already an aggregate and cannot be further aggregated.
I created a much simpler data set to show the issue.
Here is the data:
client order item amount
a 111 x 10
a 111 y 20
a 222 x 10
a 222 z 30
a 333 w 60
b 444 x 20
b 555 x 20
b 555 y 30
b 666 z 40
What I want is the average and median order amounts by client - like this:
client ave order amt median order amt
a 43.33 40
b 36.67 40
For the ave, I can get around this by creating a custom calculation to get the right number, but I can't for the median.
So I need to roll up to the client/order level before I can do this calculation. I would prefer not to do this in the data source so I am looking for assistance.
I am attaching my workbook. THanks in advance for any help you can provide.