I have two tables in a datamodel connected via sailingKey which is a unique identifier. They have crossfiltering enabled, as seen here:
In the data I have different itineraries (e.g. from A-B and C-D, ...). Each itinerary consists of multiple sailing keys.
I want to calculate the median of the metric for each itinerary but only for those sailing keys that have a value greater than 0 in the metric. The metric comes from the shipreadings table, everything else from dimsailing.
I have a partially working formula which changes based on what I filter but I do not want the number to change: