Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Songjiao
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?

Thanks in advance!

1 ACCEPTED SOLUTION

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.
 
1.png2.png

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

3.png
4.png

 

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

Best Regards,
Angelia

View solution in original post

4 REPLIES 4
Thiyags
Helper II
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

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

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.
 
1.png2.png

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

3.png
4.png

 

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

Best Regards,
Angelia

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.