Forum Discussion

fraza's avatar
fraza
Frequent Visitor
4 years ago
Solved

Calculated Index column based on groups

Can anyone help me with this.?     I need make the calculated column on the right hand side that only increments when the original field changes its value.   Noticed that the increments s...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    fraza 

    This is logically not possible since you don't have a field to group by. As the workaround, you could add an index column and flag column before getting the rank, which is a bit confusing.

     

    1. add an index column in power query

    2. create a flag column 

    Flag =
    var current_=[Original]
    var current_1= CALCULATE(MAX([Original]),FILTER('Table',[Index]=EARLIER([Index])-1))
    var current_2 = CALCULATE(MAX([Original]),FILTER('Table',[Index]=EARLIER([Index])-2))
    var index_1=CALCULATE(SUM('Table'[Index]),FILTER('Table',[Index]=EARLIER([Index])-1))
    var index_2=CALCULATE(SUM('Table'[Index]),FILTER('Table',[Index]=EARLIER([Index])-2))
    Return
    IF(current_=current_1&&current_1=current_2,index_2,IF(current_=current_1,index_1,[Index]))
     
    3. create the rank column

     

    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

fraza 

This is logically not possible since you don't have a field to group by. As the workaround, you could add an index column and flag column before getting the rank, which is a bit confusing.

 

1. add an index column in power query

2. create a flag column 

Flag =
var current_=[Original]
var current_1= CALCULATE(MAX([Original]),FILTER('Table',[Index]=EARLIER([Index])-1))
var current_2 = CALCULATE(MAX([Original]),FILTER('Table',[Index]=EARLIER([Index])-2))
var index_1=CALCULATE(SUM('Table'[Index]),FILTER('Table',[Index]=EARLIER([Index])-1))
var index_2=CALCULATE(SUM('Table'[Index]),FILTER('Table',[Index]=EARLIER([Index])-2))
Return
IF(current_=current_1&&current_1=current_2,index_2,IF(current_=current_1,index_1,[Index]))
 
3. create the rank column

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.