Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Replace values in column

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

Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    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

1 Reply

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    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