Forum Discussion

samdep's avatar
samdep
Advocate II
3 years ago
Solved

Difference Between Two Related Rows in a Table

Hi Community,   I have a table, similar to the below, where I'd like to show the difference between the manager's rating of the direct report and the direct report's rating of themselves. I'd like ...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi samdep ,

    First, please make sure that what you are creating is a calculated column not a measure, then update its formula as below to get the expected result:

    Judgment Variance = 
VAR _nextindex =
    MINX (
        FILTER (
            'Assessment',
            'Assessment'[Index] > EARLIER ( [Index] )
                && 'Assessment'[Manager & Direct Report] = 'Assessment'[Manager & Direct Report]
        ),
        [Index]
    )
VAR _nextrating =
    MAXX (
        FILTER (
            'Assessment',
            'Assessment'[Index] = _nextindex
                && 'Assessment'[Manager & Direct Report] = EARLIER ( 'Assessment'[Manager & Direct Report] )
        ),
        [Judgment]
    )
RETURN
    IF (
        ISBLANK ( _nextrating )
            || _nextrating = 'Assessment'[Judgment],
        BLANK (),
        ABS ( 'Assessment'[Judgment] - _nextrating )
    )

    Best Regards

amitchandak's avatar
amitchandak
Super User
3 years ago

samdep , try like

 

Judgment Variance =
VAR _MAX = MAXX(FILTER('Assessments', 'Assessment'[Index] < EARLIER([Index]) && 'Assessment'[Manager & Direct Report] = EARLIER('Assessment'[Manager & Direct Report])), 'Assessment'[Index])

 

VAR _RATING = 'Assessment'[Judgment] - MAXX(FILTER('Assessments', 'Assessment'[Index] =_max && 'Assessment'[Manager & Direct Report] = EARLIER('Assessment'[Manager & Direct Report])), 'Assessment'[Judgment])

 

RETURN

IF(_RATING = 'Assessment'[Judgment], BLANK(),
IF(_RATING = 0, BLANK(), ABS(_RATING))
)

 

 

Power BI DAX- Earlier, I should have known Earlier: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8