Indexing Data :)

[Summer_PBI]

Hello,

I would please like some help to create an index table.

I have a 'date' table to which 'my data' table is joined to by 'date'. - format e.g 01/04/2021

 Suppose I have a created a pivot  table like below which has data by financial years and months.

MY DATA
Year	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
2017/2018	4	6	8	4	2	7	4	2	6	8	8	7
2018/2019	9	7	3	6	6	8	6	9	7	3	2	5
2019/2020	8	5	6	9	1	9	9	6	8	8	4	9

This is the Index table Id like to create

Indexing
Year	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
2017/2018	0.73	1.09	1.45	0.73	0.36	1.27	0.73	0.36	1.09	1.45	1.45	1.27
2018/2019	1.52	1.18	0.51	1.01	1.01	1.35	1.01	1.52	1.18	0.51	0.34	0.85
2019/2020	1.17	0.73	0.88	1.32	0.15	1.32	1.32	0.88	1.17	1.17	0.59	1.32

Essentially for Indexing

 2017/2018 Apr takes the value for my data 2017/2018 Apr and divides it by the average of  entire Financial Years value from apr  - mar 2017/2018 - see example below. 

 4 / Average of (4    6          8          4          2          7          4          2          6          8          8          7)

Results = 0.73

Does anyone have any idea how I can do this in PowerBI using Dax or any other suitable way please.

Would be much appreciated.

Thank you 🙂

[Greg_Deckler]

@Summer_PBI Try:

Measure Ratio =
  VAR __Value = [Measure] //whatever you currently have in your matrix
  VAR __Average =
    AVERAGEX(
      ADDCOLUMNS(
        SUMMARIZE(ALLEXEPT('Table',[Year]),[Month]),
        "__Value",[Measure]
      ),
      [__Value]
    )
  VAR __Result = DIVIDE(__Value, __Average)
RETURN
  __Result