Finding the difference between values in the same column depending on another column.

[Anonymous]

Hi,

I am looking to find the difference between each of company 1's rate and the max of the rest of the companies rates for each year. A simplified version of the data may look something like this. 

Year	Company	Rate
2018	1	0.02
2019	1	0.023
2018	2	0.01
2019	2	0.018
2018	3	0.011
2019	3	0.012

My aim is to draw a line graph representing the market margin between company 1 (always the highest rate) and the rest of the market for each year. So in the simplied version for 2018 the margin would be 0.02 - 0.011 - 0.009. 

I'm unsure of how to do this in DAX. I imagine that the Filter function is used but I cannot seem to get the filter function to work inside a MAX function.  

Thanks for your time,

Sean. 

[Jihwan_Kim · Accepted solution]

Expected result : =

VAR currentyear =

MAX ( data[Year] )

VAR companyonerate =

CALCULATE (

SUM ( data[Rate] ),

FILTER ( ALL ( data ), data[Year] = currentyear && data[Company] = 1 )

)

VAR othersmax =

MAXX ( FILTER ( data, data[Company] <> 1 ), data[Rate] )

RETURN

IF ( HASONEVALUE ( data[Year] ), companyonerate - othersmax )

[amitchandak]

@Anonymous , Based on what I got

calculate(max(Table[Rate]) -Min(Table[Rate]) , allexcept(Table, Table[Year]))