Linear Interpolation of Multiple Data Series

[mcstunner]

Hello,

I am trying to create individual linear interpolations on multiple data series, and then average the resulting interpolations.  I have a simple data set below, but my actual data set is very large with hundreds of series' and thousands of values.

Series	X Value	Y Value
Series 1	2	20
Series 1	4	20
Series 1	6	20
Series 1	8	20
Series 2	3	30
Series 2	6	30
Series 2	9	30
Series 2	12	30

I have created a separate DIM table with a column of numbers from 1 to 12 that I would like to interpolate against as my x-axis.  I would like to create a measure that interpolates each series, and would like to average the resulting interpolations.  Any thoughts would be greatly appreciated.  

The Linear Interpolation Post  by  @gregdeckler has gotten me close, but only meets the intended purpose if I have one series of data.  Thanks in advance for any insights!!

Mike

[Greg_Deckler · Accepted solution]

@mcstunner Yikes! That formula is due for an overhaul anyway. Try this, PBIX is attached below signature.

Series 1 = 
    VAR __Table = FILTER('Table',[Series] = "Series 1")
    VAR __x3 = [Column1]
    VAR __match = MAXX(FILTER(__Table,[X Value] = __x3),[Y Value])
    VAR __x1 = MAXX(FILTER(__Table,[X Value] < __x3),[X Value])
    VAR __x2 = MINX(FILTER(__Table,[X Value] > __x3),[X Value])
    VAR __y1 = MAXX(FILTER(__Table,[X Value] = __x1), [Y Value])
    VAR __y2 = MAXX(FILTER(__Table,[X Value] = __x2), [Y Value])
    VAR __Result = IF( __match <> BLANK(),__match, __y1 + DIVIDE((__x3 - __x1) * (__y2 - __y1), (__x2 - __x1)))
RETURN
    __Result



Series 2 = 
    VAR __Table = FILTER('Table',[Series] = "Series 2")
    VAR __x3 = [Column1]
    VAR __match = MAXX(FILTER(__Table,[X Value] = __x3),[Y Value])
    VAR __x1 = MAXX(FILTER(__Table,[X Value] < __x3),[X Value])
    VAR __x2 = MINX(FILTER(__Table,[X Value] > __x3),[X Value])
    VAR __y1 = MAXX(FILTER(__Table,[X Value] = __x1), [Y Value])
    VAR __y2 = MAXX(FILTER(__Table,[X Value] = __x2), [Y Value])
    VAR __Result = IF( __match <> BLANK(),__match, __y1 + DIVIDE((__x3 - __x1) * (__y2 - __y1), (__x2 - __x1)))
RETURN
    __Result