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

Thiyags's avatar
Thiyags
Helper II
9 years ago

Songjiao

 

You have to create 2 measures.

 

First measure - CALCULATE(MIN(Sheet12[Values]),ALL(Sheet12[Values]))

 

Sheet12 is my table and values column contain the random values

 

Second measure - DIVIDE(SUM(Sheet12[Values]),[First Measure])

 

Change the second measure format to %.

 

Let me know if this works

  • Songjiao's avatar
    Songjiao
    Frequent Visitor
    9 years ago

    sorry, didn't make myself clear on this. 

    I want to put everything compared to my first value in the column.  

     

    in my original case, Ex. 10, 20, 25, 11, 22, 99, 87.   first number is 10, thus everything compares to 10.  if first number is 25, then I need the remaining numbers divided by 25, then create an index.  

     

    Seems the logic in your solution is to find the smallest value in a serie.  

     

    Thank you for your help on this!!

    • 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!