Tableau LOD to find median

Question

I have some data:

Inst    Dest_Group  Dest Cipn1  N
I1  C   a   43
I1  F   a   63
I1  U   a   54
I1  C   b   96
I1  F   b   3
I1  U   b   78
I1  C   c   12
I1  F   c   65
I1  U   c   49
I2  C   a   3
I2  F   a   47
etc...

My worksheet is set up so that [Dest Cipn1] is a row, and [Dest Group] is a column. They display [value] as a bar chart. [value] = {include [Inst] : sum([N])} / {fixed [Inst] : sum([N])}

This worksheet is filtered on [Inst] = I1. I would like to add a reference line that shows the median value for each bar (cell) across all the [Inst]. (In the end I will add a band that displays 25th - 75th percentile but I figured working with the median would be simpler first).

I thought this would work, but it doesn't: [AllInstMedian] = {fixed [Inst],[Dest Group], [Dest Cipn1] : Sum([N])} / {fixed [Inst] : Sum([N])}

Any suggestions? I'm attaching a sample workbook here hoping that helps .

This is cross-posted here

Thank you

Answer 1

Steve mayer commented on the tableau link posted in the question with an answer. I ended up using a Lookup trick to copy inst and then used table calculations on the 25th and 75th window_percentile.