topic Re: Subtract two values based on Rank in DAX Commands and Tips

Subtract two values based on Rank

Valentin09 — Mon, 16 Dec 2024 14:44:28 GMT

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: Name: "Aaron" / current score = 50 / previous testscore = 50 --> result: 0

Either as a measure or as a new column.

I hope somebody can help me.

Best regards,

Valentin

Re: Subtract two values based on Rank

mark_endicott — Mon, 16 Dec 2024 14:57:28 GMT

Valentin09 - See below:

VAR _name = SELECTEDVALUE ( 'Table'[Name] ) 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 ) VAR _current_minus_prev = SUM ( 'Table'[Wert] ) - _prev_test_score RETURN _current_minus_prev

You'll just need to swap out my table and column names for yours.

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

Re: Subtract two values based on Rank

Valentin09 — Mon, 16 Dec 2024 15:30:21 GMT

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?

Re: Subtract two values based on Rank

mark_endicott — Mon, 16 Dec 2024 15:47:54 GMT

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.

Re: Subtract two values based on Rank

Valentin09 — Mon, 16 Dec 2024 16:06:00 GMT

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

Re: Subtract two values based on Rank

mark_endicott — Mon, 16 Dec 2024 16:19:31 GMT

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.

Re: Subtract two values based on Rank

Anonymous — Tue, 17 Dec 2024 03:11:45 GMT

Thanks for the replies from mark_endicott.

Hi Valentin09 ,

Based on your description, I created simple data:

Please try the following DAX formula:

Difference = VAR _count=CALCULATE(COUNT('Table'[Name]),ALLEXCEPT('Table','Table'[Name])) VAR _current=MAX('Table'[Testrang]) VAR CurrentTestrang=CALCULATE(SUM('Table'[Wert]),FILTER(ALLEXCEPT('Table','Table'[Name]),'Table'[Testrang]=_current)) VAR PreviousTestrang=CALCULATE(SUM('Table'[Wert]),FILTER(ALLEXCEPT('Table','Table'[Name]),'Table'[Testrang]=_current-1)) return IF(_count>1&&_current>1,CurrentTestrang-PreviousTestrang,blank())

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Subtract two values based on Rank

Valentin09 — Tue, 17 Dec 2024 15:27:47 GMT

This works perfectly! I just had to change -1 to +1, because most recent test has the 1, the penultimate the 2 and so on.

Re: Subtract two values based on Rank

mark_endicott — Tue, 17 Dec 2024 15:36:26 GMT

Valentin09 - No problem, I missed it saying the current test was 1 in your original description.

I'm glad you've been able to amend my DAX accordingly.