Index chart - specifying a particular data value

Hi JonClemo ,

Based on what I have understood:

Your index is dynamic and changes based on the year you select in a slicer. Table store static values and cannot generate dynamic values. Calculated Columns work best when you want to categorise or want to eat up on your memory.

Suggest see this video : 

https://www.youtube.com/watch?v=03E0f-RyRn4

https://www.youtube.com/watch?v=xV-KwuSDFMQ

https://www.youtube.com/watch?v=DwuAypulTLA

Test1 = 
var index_year = 2008
var Baseline = calculate(SUM('Table'[Income]), FILTER(ALL('Table'[Year End]), 'Table'[Year End] = index_year))
RETURN
DIVIDE('Table'[Income], [Baseline])

Will try my best to explain in simple terms. I hope i make sense.

Check the Filter Part -> You are filtering the table column  [Year End] where Table[Year End] = 2018. So this will give you only rows of the table where year end = 2018. So incase your table has 100 rows out of which 5 rows are 2018. these 5 rows will be returned.

Post that you are adding the income in these 5 rows.

Now you are dividing the Income of all rows with the baseline (baseline has values only for 5 rows). So for other rows it is income divide by blank.  And for 2018, you get 1. Now change the column type to decimal you may get 0.xx values in rows where year is 2018.

I hope this made sense.

Regards,

Harsh Nathani