cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors