Forum Discussion

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 nul...

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