Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX / Measures Help - Find difference using same measure

Community, 

I am looking to return a difference between 2 values based on a lookup. The 2 values I want to find the difference of are from the same measure. Please see the example below, any help is appreciated.

 

My example data (TABLE1)

 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
For each Test ID, I get the average of the last two values using a measure. This is my code to return that value

 

Average Data Last 2 = 
CALCULATE (
    AVERAGE ( 'TABLE1'[DATA] ),
    FILTER (
        ALL ( 'TABLE1'[INDEX] ),
        'TABLE1'[INDEX] <= MAX( 'TABLE1'[INDEX] )
            && 'TABLE1'[INDEX]
                > (MAX('TABLE1'[INDEX]) - 2)
    )
) ​

 

 

This allows me to have a table that looks something like this

 

 

 

 

 

Now the part I cannot get correct, I want to find the difference between the Test ID and the Ref ID. In this example there are two Test ID's, 1 and 2, the Ref ID refers to the Test ID we are trying to evaluate against. The result should return something like below in the Difference of Test ID to Ref ID column, where the math would be (Test ID, 1, Average Data Last 2) - (Ref ID 2, Lookup Test ID, 2, Average Data Last 2) or 11.5-10 = 1

 

 

 

Any Help Is Appreciated, Thanks!!

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous try this measure to get the difference

     

    Diff Test Id to Ref Id = 
VAR __avgRefId = CALCULATE ( [Avg Last Two], FILTER ( ALL( Ref ), Ref[Test Id] = MAX( Ref[Ref Id] ) ) )
RETURN [Avg Last Two] - __avgRefId

     

1 Reply

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous try this measure to get the difference

     

    Diff Test Id to Ref Id = 
VAR __avgRefId = CALCULATE ( [Avg Last Two], FILTER ( ALL( Ref ), Ref[Test Id] = MAX( Ref[Ref Id] ) ) )
RETURN [Avg Last Two] - __avgRefId