Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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. 

 

YearCompanyRate
20181

0.02

201910.023
201820.01
201920.018
201830.011
201930.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. 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

     

    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 )
     
     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

     

    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's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Based on what I got

     

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