Replace values in column

[Anonymous]

Hello everyone.
My dataset is as follows

Server_name       Number_of_licenses

------------------------------------------

Server1                       1800

Server2                         null

Server3                         null

Server4                          200

Server5                          null

Server6                          650

Server7                          1120

What I have been trying to achieve is replace the null with the value right above, by dividing "value" to the number of nulls right below + 1. Also, the "value" should be replace with the result of the division.

For instance, the nulls below 1800 should be replaced by 600 (1800/3) and 1800 itself should also be replaced with 600.

The dataset after applying the formula should be:

Server_name       Number_of_licenses

------------------------------------------

Server1                       600

Server2                       600

Server3                       600

Server4                       100

Server5                       100

Server6                       650

Server7                      1120

Any ideas on how can I achieve this? I've been trying for some days already and I don't see any solution to this.

Thanks in advance.
Vlad

[camargos88 · Accepted solution]

Hi @Anonymous ,

First create a calculated column for ranking:

Rank = RANKX('Table'; 'Table'[Server_name];; ASC)

After create another calculted column for adjust the values:

Number_of_Licenses_2 =

VAR _rank = 'Table'[Rank]

VAR _newRank = CALCULATE(MIN('Table'[Rank]); FILTER(ALL('Table'); 'Table'[Rank] > _rank && ISBLANK('Table'[Number_of_licenses]) = FALSE()))

VAR _newRank2 = CALCULATE(MAX('Table'[Rank]); FILTER(ALL('Table'); 'Table'[Rank] <= _rank && ISBLANK('Table'[Number_of_licenses]) = FALSE()))

VAR _value = CALCULATE(DISTINCT('Table'[Number_of_licenses]); FILTER(ALL('Table'); 'Table'[Rank] = _newRank2))

RETURN IF(_newRank - _newRank2 > 0; DIVIDE(_value; _newRank - _newRank2; 0); 'Table'[Number_of_licenses])

I hope it helps.

If you consider it as a solution, please mark as solution and kudos.

Ricardo