I have somewhat of a complex color-coding question I'm hoping someone can help me out with.
I've made a dashboard such that when the user selects the time range as the last 2 weeks, with the Team Name's content they're looking to analyze, and maybe the location of Production as San Francisco (for instance), they'll get a list of all posts produced by Team A in the last 2 weeks in San Francisco, along with their associated metrics (see screenshot). I've added some easy gradient shading to a few of the metrics already but now have a new requirement from my boss -- he would like to see these metrics color-coded based on what is performing above and below the medians for each metric, out of ALL content from the timeframe within which the filtered posts were published.
I realize this is probably an IF THEN calculated field for each individual metric, but I'm stuck on how to calculate the Median Engagement Score for the past week of content, for instance, when the filter I'm applying is only for one team (and there are 4 teams and I want to see the Median among all Team's and Production Location's content).
Hopefully this question makes sense. Thanks very much to this community!
P.S. there is [Date Published] data for each post which is a Date & Time dimension.