Unexpected result with Distinct Median

Question & Answer
, , ,
1

Hi QuickSight Experts -

Can someone please help me calculate median of sales_amt in a year/week (ignoring duplicate entries for each sales_id and sales_amt.

image.

I tried Notion – The all-in-one workspace for your notes, tasks, wikis, and databases. but it giving me unexpected result, not sure if i am missing something here. Below is unexpected result and calculated field logic - median outputs 1,1 instead of 125, 125

image
image1303×676 34.2 KB

Calculated_Fields

  1. Distinct Count ==== distinctCountOver({sales_amt},[{sales_id}],PRE_FILTER)
  2. Ifelse Rank ====
    ifelse( rank([{sales_amt} ASC], [{sales_id}], PRE_AGG) = 1, {Distinct Count}, NULL )
2

@bipshik
if your data look exactly like this, QS will remove duplicates and it will look like this
image

Do you want to display 125 (median) in a new column(after the amount)?

if you convert year+week into date then you might be able to use periodToDateMedian

3

sorry unfortunately this function doesn’t fulfill my requirement. Though thanks for the help @nshah-quicksight

4

I was able to resolve this myself.
posting here steps
Step 1 - Derived median of sales_amt partition by sales_id, lets call sales_median_by_id == median({sales_amt},[{sales_id}])
Step 2 - On visual, took median of sales_median_by_id

1 Like