Calculations by Rank

[PMF999]

I

In my Var column I'd like to calculate the variance between the current rank and the next rank down/lower rank (for each Type). Eg: for Type B (on row 7) rank 4 is 120 less rank 3 (on row 6) is 105 thefore variance = 15.  Many thanks in advance!

[ChrisMendoza · Accepted solution]

@PMF999 -

Would the below work for you?

Var = 
VAR nextRank = Table1[Rank] + 1
VAR differnce = 
    CALCULATE(
        VALUES(Table1[Value]),
        FILTER(ALLEXCEPT(Table1,Table1[Type]),Table1[Rank] = nextRank)
    ) - Table1[Value]
RETURN
    IF(
        differnce >= 0,
        differnce,
        BLANK()
    )

[ChrisMendoza · Accepted solution]

@PMF999 -

The entire code block is your calculated column. Documentation on using VAR can be found @ https://docs.microsoft.com/en-us/dax/var-dax and a nice example article can be found @ https://www.sqlbi.com/articles/variables-in-dax/