Struggling to plot calculated median value with observed historic years of data

[cld32650]

I am trying to plot the historic data and median value (data sampled daily over many years) of a timeseries using a measure but PBI is giving me a lot of trouble for something that seems like it should be simple. My table is formatted as follows under the following conditions:

• the "Date" column is mapped to the same day of year for this year. This is important to plot all years of data on top of each other for corresponding days.
• If the data is sourced from the current year's data, the year is labelled "Observed"
• Site and Region are slicer selections.
• If the year is labelled as "median", the Site, Region, and Year columns are the only columns with data. All others are blank.

Site	Region	Day of Year Formatted as Date	Year	Date	Value
SiteA	Region1	1/1/2026	2020	1/1/2020	1
SiteA	Region1	1/2/2026	2020	1/2/2020	2
SiteA	Region1	1/1/2026	2021	1/1/2021	3
SiteA	Region1	1/2/2026	2021	1/2/2021	4
SiteA	Region1	1/1/2026	Observed	1/1/2026	5
SiteA	Region1	1/2/2026	Observed	1/2/2026	6
SiteA	Region1	blank()	Median	blank()	blank()
Site[...]	Region[...]	[...]	etc	[...]	[...]

It appears I cannot calculate the median in a measure and add it as a secondary series on the y-axis nor plot it in the secondary y-axis. As a result, I'm attempting to extend the datatable with rows for "Median", with the idea being to add the years to the legend and filter using a slicer. I would like the slicer selection of median to calculate the median value each day across the years of data. Below is one of many versions of the measure I have written to attempt the plotting the median value with the observed and historic values. Why doesn't the median value show in the plot?

VAR SelectedRegion = SELECTEDVALUE(AppendedTables2[Region])
VAR SelectedSite      = SELECTEDVALUE(AppendedTables2[Site])
VAR Label             = SELECTEDVALUE(AppendedTables2[Year])
VAR SelectedDate      = SELECTEDVALUE(AppendedTables2[Day of Year Formatted as Date])

VAR MedianResult = 
    CALCULATE(
        MEDIAN(AppendedTables2[Value]),
        REMOVEFILTERS(AppendedTables2[Year]),
        REMOVEFILTERS(AppendedTables2[NumericYear]),
        AppendedTables2[Reservoir]    = SelectedRegion,
        AppendedTables2[Object.Slot]  = SelectedSite,
        AppendedTables2[Day of Year Formatted as Date] = SelectedDate,
        NOT ISBLANK(AppendedTables2[Value])
    )

VAR TraceResult = 
    CALCULATE(
        AVERAGE(AppendedTables2[Value]),
        AppendedTables2[Reservoir]   = SelectedReservoir,
        AppendedTables2[Object.Slot] = SelectedSlot,
        AppendedTables2[Trace]       = Label
    )

RETURN
    IF(Label = "Median", MedianResult, TraceResult)

 The figure shows the Median value does not plot.

Any help would be greatly appreciated.

[SamInogic]

Hi,

As per your setup, the main reason the Median line is not appearing is because your measure relies on:

SELECTEDVALUE(AppendedTables2[Day of Year Formatted as Date])

In a line chart, the context contains multiple dates, so SELECTEDVALUE() returns BLANK, which causes your Median Result to return no value → nothing gets plotted.

Fix (Use row context instead of SELECTEDVALUE)

Replace your date logic with the axis context using MAX() (or MIN()):

VAR SelectedDate =

    MAX(AppendedTables2[Day of Year Formatted as Date])

VAR MedianResult =

    CALCULATE(

        MEDIAN(AppendedTables2[Value]),

        REMOVEFILTERS(AppendedTables2[Year]),

        AppendedTables2[Reservoir] = SELECTEDVALUE(AppendedTables2[Region]),

        AppendedTables2[Object.Slot] = SELECTEDVALUE(AppendedTables2[Site]),

        AppendedTables2[Day of Year Formatted as Date] = SelectedDate,

        NOT ISBLANK(AppendedTables2[Value])

    )

RETURN

IF(

    SELECTEDVALUE(AppendedTables2[Year]) = "Median",

    MedianResult,

    AVERAGE(AppendedTables2[Value])

)

(SELECTEDVALUE(Date) →  fails in charts (multiple values in context)
• MAX(Date) or MIN(Date) → works per data point on axis
• Your “Median row in table” approach is fine, but the measure must respect visual context

Hope this helps.

Thanks!

[cld32650 · Accepted solution]

Thanks for the response. This did not work seemlessly for me, but it did put me on the right track conceptually to get the figure working. 
For others that come to this - I had to extend the calculated table for the median "Year" so that there was a value in each of the rows of "Day of Year Formatted as Date" column. After performing that update, the following measure achieved the desired output:

Historic Median and or Observed = 

VAR SelectedDate =
    MAX(AppendedTables3[Day of Year Formatted as Date])

VAR MedianResultFabric =
    CALCULATE(
        MEDIAN(AppendedTables3[Value]),
        REMOVEFILTERS(AppendedTables3[Year]),
        AppendedTables3[Region] = SELECTEDVALUE(AppendedTables3[Region]),
        AppendedTables3[Site] = SELECTEDVALUE(AppendedTables3[Site]),
        AppendedTables3[Day of Year Formatted as Date] = SelectedDate,
        NOT ISBLANK(AppendedTables3[Value])
    )

VAR SelectedRegion = SELECTEDVALUE(AppendedTables3[Region])
VAR SelectedSite      = SELECTEDVALUE(AppendedTables3[Site])
VAR SelectedDate      = SELECTEDVALUE(AppendedTables3[Day of Year Formatted as Date])
VAR Label             = SELECTEDVALUE(AppendedTables3[Year])

VAR CalculatedResult = CALCULATE(AVERAGE(AppendedTables3[Value]),
    AppendedTables3[Region] == SelectedRegion &&
    AppendedTables3[Site] == SelectedSite &&
    // AppendedTables3[Day of Year Formatted as Date] == SelectedDate && 
    AppendedTables3[Year] == Label)

Var medianResult = 
    CALCULATE(Median(AppendedTables3[Value]),
    REMOVEFILTERS(AppendedTables3[Year]),
    AppendedTables3[Region] == SelectedRegion,
    AppendedTables3[Site] == SelectedSite
    )


RETURN
IF(
    SELECTEDVALUE(AppendedTables3[Year]) = "Median",
    medianResult,
    CalculatedResult

)