Calculating Median of aggregated data

[Anonymous]

Hi everyone,

I currently have a main table which has 3 columns:

• User
• Week Commencing 
• Sales Volume

I also have 2 joining tables which I would like to use within separate slicers to filter the main table based on user attributes:

• Country table (User, Country Name)
• Team table (User, Team Name)

I would like to be able to achieve the following to be shown in a clustered column and line chart:

• Clustered Column - To be able to show the overall total weekly sales each week 
• Line - To calculate the median value of the overall total weekly sales over the last 52 weeks, so that this can be shown as a static line
• For example, I would like to be able to create a chart where if I selected America in my Country table, I would see the total weekly sales overall for American users by week and a single horizontal line showing the median total weekly sales performance over the time period for American users only (i.e. the chart reflects the slicers selected)

Please can someone advise how to achieve this as I'm struggling to understand how to do this? Any help would be much appreciated please. 

Thanks in advance,

[lbendlin · Accepted solution]

Your line cannot be static since you have country and team filters.  Use a measure to calculate the median based on the filter selections and then feed that measure into the line value.

[Anonymous]

Thank you 😃 I've tried calculating the median with the use of ALLEXCEPT which seems to have hopefully worked.