I'll start by saying that as tempting as it becomes to use LOD expressions to solve everything, there are still cases where we have to go old school. Let's start with confirming definitions: median in your use case is the middle [Value] in your data set - since there are 27 values across all [Inst], the Median is the 14th Value (see screenshot below).
If that is the median you are looking for, I can't see a way of determining the median using an LOD expression (I'm pretty good with LOD, but someone out there, please prove me wrong), because LOD values are not in any particular sort order. You would have to be able to index the values in order to find the middle one.
So I used the old school table calculation using WINDOW_MEDIAN, and the LOOKUP trick to make sure that no [Inst] were filtered out of the resulting Viz, because you want the median value to be across all [Inst].
Remove the [Inst] filter, because we need all values in the data source to find the Median
Create a calculated field [Median]
Add [Median] to the Level of Detail of your Viz, and use the Advanced Table Calculation dialog to move everything to Addressing (no Partitioning because you want the Median across all values)
Add [Median] as a reference line to your chart
Create a calculated field [Inst Lookup]
Add [Inst Lookup] to filters and set it to "Inst1"
Confirm that Median is still the median for the entire data source
tablueahelp.twbx 27.5 KB
Thanks for looking into this. I am actually trying to find the median across [Inst] for each cell. Thus there would be a different median for each bar. In my example data, it would be the median of (.092873, .007614, .02514)... so .02514 for the first bar. Do you think that would require LOD? Maybe I am over complicating it. I assumed I would need LOD because my dimension filter is [Inst] = I1 but I want to find a median across a broader selection (all the inst) for each [Dest_Group] [Dest Cipn1] bar cell.
Does that make sense?
I'm confused by your definition of median.
Over what range of values is the median? Do you expect a different median for each Dest Group / Dest Cipn1 combination, thus a different reference line for every single bar? Is this what you are looking for?
The above solution should give you what you are looking for - it is just down to setting the correct addressing/partitioning to determine how to calculate the median.
Yes I do. What happened to your last reply? I think that was what I was looking for (thank you) (sorry I'm taking a little time processing it). I just changed the ref line from per pane to per cell.
Here is the workbook with the complete solution. I also changed the reference lines from Per Pane to Per Cell, and have medians for every bar across all Inst.
The key to the whole thing is the table calculation filter - even though it filters only Inst1 values, the Median table calculation still calculates over the entire data source.
Still don't think this is possible with LOD calculations (but challenge anyone to find a way!), so hopefully this is a workable solution.
tablueahelp.twbx 25.3 KB
Steve - Thanks so much! Yes the lookup trick and the table calculation filter really bring everything together - I'll have to look more into those