Forum Discussion

Summer_PBI's avatar
Summer_PBI
New Member
3 years ago

Indexing Data :)

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 ðŸ™‚

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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