Forum Discussion

Valentin09's avatar
Valentin09
Regular Visitor
1 year ago
Solved

Subtract two values based on Rank

I have the following table:   I want to subtract the current testscore (Testrang = 1) with the previous testscore (Testrang = 2). In this example I want to have the following calculation: Nam...
  • mark_endicott's avatar
    mark_endicott
    1 year ago

    Ok so in that case the visual must already have been filtered to the test type. If you have multiple tests per person, within the same type, how are you filtering it to show the 2 most recent results? If you are not filtering it, and you are using measures to display columns 2 & 3 of your visual, you'll need to factor this into the DAX, I suggest something like this:

     

    VAR _name =
    SELECTEDVALUE ( 'Table'[Name] )
VAR _test_type = 
    SELECTEDVALUE( 'Table'[Test] )
VAR _recent_test = 
    MAX ( 'Table'[Testrang] )
VAR _recent_test_score = 
    CALCULATE (
        SUM ( 'Table'[Wert] ),
        REMOVEFILTERS ( 'Table'[Testrang] ),
        'Table'[Testrang] = _recent_test
            && 'Table'[Name] = _name
            && 'Table'[Test] = _test_type
    )
VAR _prev_test = _recent_test - 1
VAR _prev_test_score =
    CALCULATE (
        SUM ( 'Table'[Wert] ),
        REMOVEFILTERS ( 'Table'[Testrang] ),
        'Table'[Testrang] = _prev_test
            && 'Table'[Name] = _name
            && 'Table'[Test] = _test_type
    )
VAR _current_minus_prev =
    _recent_test_score - _prev_test_score
RETURN
    _current_minus_prev

     

    If this works for you please accept as the solution for visibilty of others. 

Valentin09's avatar
Valentin09
Regular Visitor
1 year ago

Thanks a lot for your response, but unfortunately it isn't working as I wished. Here is the formula I have used:

 

And here is the visual with the scores:

 

What am I doing wrong?

  • mark_endicott's avatar
    mark_endicott
    Super User
    1 year ago

    Valentin09  - Do you have multiple test types? This was't specified, so it looks like my measure is returning the first result it finds, regardless of test type.

     

    In which case use this:

     

    VAR _name =
    SELECTEDVALUE ( 'Table'[Name] )
VAR _test_type = 
    SELECTEDVALUE( 'Table'[Test] )
VAR _prev_test =
    SELECTEDVALUE ( 'Table'[Testrang] ) - 1
VAR _prev_test_score =
    CALCULATE (
        SUM ( 'Table'[Wert] ),
        REMOVEFILTERS ( 'Table'[Testrang] ),
        'Table'[Testrang] = _prev_test
            && 'Table'[Name] = _name
            && 'Table'[Test] = _test_type
    )
VAR _current_minus_prev =
    SUM ( 'Table'[Wert] ) - _prev_test_score
RETURN
    _current_minus_prev

     

    If this works for you please accept as the solution for visibilty of others. 

    • Valentin09's avatar
      Valentin09
      Regular Visitor
      1 year ago

      Thanks a lot, but this has no effect on the values. Yes I have different Testtypes.