cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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%

How do I create a new measure based on DAX functions?

1 ACCEPTED SOLUTION
Employee

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]))`

Best Regards,
Angelia

4 REPLIES 4
Helper II

@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

Frequent Visitor

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

Employee

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]))`

Best Regards,
Angelia

Frequent Visitor

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors