Avg nested with percentileContOver not giving expecpted results

[Wanli_Pu]

Hello everyone,

I am using quicksight demo dataset “Sales Pipeline” to explain the issue.

First: calculate the median of “Forecasted Monthly Revenue” in each Region and Segment combination, which is in the following screenshot.

Next: calculate average in each Region, I did it in excel for comparison

When I trying to do it using this calculated field

avg(
    percentileContOver({Forecasted Monthly Revenue}, 
    50, 
    [Region, Segment], 
    PRE_AGG)
)

What I get is in the following screenshot, it’s different from excel calculation, you can verify manually as well.

Also tried this formula

avgOver((
    percentileContOver({Forecasted Monthly Revenue}, 
    50, 
    [Region, Segment], 
    PRE_AGG)
),
[Region],
PRE_AGG
)

Both give the same results

What did I miss here?

Thanks!

[Wanli_Pu]

I figured out the reason.

P50 values will be assigned to all rows. When avg function is called, it actually calculates average across all rows (for the region+segment) instead of unique region+segment combinations (3 values).