reiterate a column to match value and then proceed to a computation

[IlliaShliapuhin]

Hello Guys,

I would like to find a solution to make Power Bi perform some computations by taking a previously available value using its rank.

Here is my table. In column Rank, I just got ranks for some variables. In column Max, I just asked to show the maximal Rank for this subgroup. In the last column, I told Power Bi to take the value belonging to the last rank(24) and do some calculations. Now, I want to know how I should do to use the last available value, which is 5.4, and do some calculations, let's imagine 5.4+1, for the rank n23. After getting 6.4, do the same (6.4+1) for the rank 22.

I appreciate your help !

[tamerj1]

Hi @IlliaShliapuhin 
Please refer to attached sample file. Hope this is what you're looking for.

Result = 
VAR CurrentRank = 'Table'[Rank]
VAR CurrentSubTable = 
    CALCULATETABLE ( 
        'Table', 
        ALLEXCEPT ( 'Table', 'Table'[Group], 'Table'[Subgroup] ) 
    )
VAR MaxRank = MAXX ( CurrentSubTable, 'Table'[Rank] )
VAR Intag = MAXX ( CurrentSubTable, 'Table'[Intag] )
RETURN
    MaxRank - CurrentRank + Intag

[IlliaShliapuhin]

Hi @tamerj1 

I truly appreciate your help but it is not exactly what I need.
The model is ok, but I don't need to increment 1 to each value...
Let's take group 2 subgroup C of your sample. The first step is to get the value in the column Result from column Intag, which is 3.9 => this is what we acheived. But then, I need to use this same value of 3.9 for the next row ranked 4, to calculate my second value : 3.9*(1-.1) = 3.51. Then, for the rank 3,i take the previous value which is 3.51 and do the same : 3.51*(1-.1)
Hope it makes sense,
Is it feasible, you think ?

[tamerj1]

@IlliaShliapuhin 
Please refer to updated sample file 

Result = 
VAR C = 0.1
VAR CurrentRank = 'Table'[Rank]
VAR CurrentSubTable = 
    CALCULATETABLE ( 
        'Table', 
        ALLEXCEPT ( 'Table', 'Table'[Group], 'Table'[Subgroup] ) 
    )
VAR T1 = TOPN ( 1, CurrentSubTable, 'Table'[Rank] )
VAR MaxRank = MAXX ( T1, 'Table'[Rank] )
VAR Intag = MAXX ( T1, 'Table'[Value] )
VAR P = MaxRank - CurrentRank
VAR Value1 = ( 1 - C ) ^ P * Intag
VAR T2 = FILTER ( CurrentSubTable, 'Table'[Rank] >= CurrentRank && 'Table'[Rank] < MaxRank )
VAR Value2 = SUMX ( T2, POWER ( 1 - C, P - 1 ) * C * 'Table'[Value] ) 
RETURN
    Value1 + Value2

[IlliaShliapuhin]

@tamerj1 

It is powerful what you have done, I will study it but unfortunately, it doesn't respond to my question =(
Could you, please take a look at the table below, I tried to exemplify the needed outcome

[tamerj1 · Accepted solution]

@IlliaShliapuhin 
Please refer to attached sample file with the solution

Result = 
VAR C = 0.1
VAR CurrentRank = 'Table'[Rank]
VAR CurrentSubTable = 
    CALCULATETABLE ( 
        'Table', 
        ALLEXCEPT ( 'Table', 'Table'[Group], 'Table'[Subgroup] ) 
    )
VAR T1 = TOPN ( 1, CurrentSubTable, 'Table'[Rank] )
VAR MaxRank = MAXX ( T1, 'Table'[Rank] )
VAR Intag = MAXX ( T1, 'Table'[Intag] )
VAR P1 = MaxRank - CurrentRank
VAR Value1 = ( 1 - C ) ^ P1 * Intag
VAR T2 = FILTER ( CurrentSubTable, 'Table'[Rank] >= CurrentRank && 'Table'[Rank] < MaxRank )
VAR Value2 = 
    SUMX ( 
        T2, 
        VAR P2 = COUNTROWS ( FILTER ( T2, 'Table'[Rank] <= EARLIER ( 'Table'[Rank] ) ) )
        RETURN
            POWER ( 1 - C, P2 - 1 ) * 'Table'[Rank] 
    ) 
RETURN
    Value1 + Value2

[IlliaShliapuhin]

Hi @tamerj1 
It is perfect, I am very grateful for your assistance!
Thank you very much, boss! 😃

[tamerj1]

@IlliaShliapuhin 

Now clear. I'll have a lookout at it tomorrow morning 

[IlliaShliapuhin]

Hi @FreemanZ ,
Thank you, it works, but in my case I need something more sophisticated, please
Actually there is a formula to apply for my initial value from column Intang. Then, the second value i obtain should be the initial input for my subsequent calculation.
Please look at my screenshot below with the illustrated logic

Many thanks !

[FreemanZ]

hi   @IlliaShliapuhin

try to add a column like this:

Result =
VAR _value = MAX('Table'[MAX])
VAR _max = MAX('Table'[Intang])
RETURN _value + _max - [Rank]