Forum Discussion

bob57's avatar
bob57
Helper IV
3 years ago
Solved

Calculated column.

Hello, Here is the original table: I need a calculated column that will for each category display the difference in rating from the base rating for that category. Here are what the column re...
Need Assistance
  • Anonymous's avatar
    Anonymous
    3 years ago

    hello bob57 ,
    pelase try :

    Result = 
var _Currentcategory=Table1[Category Id]
var _Previousrating= MINX(FILTER(Table1,Table1[Rating]<EARLIER(Table1[Rating]) && Table1[Category Id]=_Currentcategory),Table1[Rating])
var _result=Table1[Rating]-_Previousrating
return IF(ISBLANK(_Previousrating),0,_result)

     

     

    if you find this helpful, please consider accepting it as a solution and a kudos is appreciated

bob57's avatar
bob57
Helper IV
3 years ago

I jumped the gun. There is still one small issue. The calculation must be relative to the Base. So with Category Id 3, record Id = 8 should be 0 (Base = TRUE) and record Id = 9 should be -1.4 (47.6 - 49). So sorry for not being more thorough at first glance.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    is this the desired result?

    Result = 
var _Currentcategory=Table1[Category Id]
var _Previousrating= MINX(FILTER(Table1,Table1[Rating]<EARLIER(Table1[Rating]) && Table1[Category Id]=_Currentcategory && Table1[Base] = "true"),Table1[Rating])
var _Previousrating2= MINX(FILTER(Table1,Table1[Rating]>EARLIER(Table1[Rating]) && Table1[Category Id]=_Currentcategory && Table1[Base] = "true"),Table1[Rating])
var _result=Table1[Rating]-_Previousrating -_Previousrating2
return IF(Table1[Base]="true",0,_result)

     

     

    • bob57's avatar
      bob57
      Helper IV
      3 years ago

      Yes. That does it. Thanks you!