Forum Discussion

Songjiao's avatar
Songjiao
Frequent Visitor
9 years ago
Solved

create index based on first row value

I'd like to create an index based on first value.   Ex. I have a colume of data (random):  10, 20, 25, 11, 22, 99, 87 I'd like to have a result like below: 100%, 200%, 250%, 110%, 220%, 990%, 870% ...
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Songjiao,

    In the Power Query Editor, under Add column, click add index column highlighted in yellow. You will get the index from first rows shown in second screenshot.
     


    Then create a calculated column to get the first row value, create a measure to calculated percentage based on first value. Please see the result shown in table.

     

    First value = MINX(FILTER(Table5,Table5[Index]=1),Table5[values])
    result = DIVIDE(SUM(Table5[values]),AVERAGE(Table5[First value]))


     

    If you have any other issue, please feel free to ask.

    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi Songjiao,

In the Power Query Editor, under Add column, click add index column highlighted in yellow. You will get the index from first rows shown in second screenshot.
 


Then create a calculated column to get the first row value, create a measure to calculated percentage based on first value. Please see the result shown in table.

 

First value = MINX(FILTER(Table5,Table5[Index]=1),Table5[values])
result = DIVIDE(SUM(Table5[values]),AVERAGE(Table5[First value]))


 

If you have any other issue, please feel free to ask.

Best Regards,
Angelia

Songjiao's avatar
Songjiao
Frequent Visitor
9 years ago

thank you very much for your help.  this solved it!