Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
bob57
Helper IV
Helper IV

Calculated column.

Hello,

Here is the original table:

bob57_1-1667082968029.png

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:

bob57_2-1667083152190.png

Thank you,

Bob

1 ACCEPTED SOLUTION
eliasayy
Impactful Individual
Impactful Individual

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)

 

Screenshot 2022-10-30 010315.png

 

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

View solution in original post

6 REPLIES 6
bob57
Helper IV
Helper IV

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.

bob57_0-1667086648861.png

 

eliasayy
Impactful Individual
Impactful Individual

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)

 

Screenshot 2022-10-30 020156.png

Yes. That does it. Thanks you!

bob57
Helper IV
Helper IV

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

eliasayy
Impactful Individual
Impactful Individual

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:

Screenshot 2022-10-30 012613.png

eliasayy
Impactful Individual
Impactful Individual

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)

 

Screenshot 2022-10-30 010315.png

 

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors