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 results would be:

Thank you,

Bob

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    This solution works beautifully! Now I have to figure out how it works. Thank you so much!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      basically, in the MINX,in table selection , you want to filter the table first to show it - 1 row
      hence using the earlier , since we used eralier([rating]) this will show 1 row before, the earlier can be used with ealier([rating],2) to show 2 rows before... 
      hence we filtered the table to show the earlier result and substract current wit hearlier ,then we want to go from down to up so we use MINX to pick up the last one instead of going from up to down, in that case you will use MAXX like the example below:

       

  • 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)